relationships select list

A place for users and developers of the Xataface to discuss and receive support.

relationships select list

Postby linux123 » Sat Jul 04, 2009 11:35 am

Hello,

there is a 2way relationship between table "Shops" and "Products".
Obviously, a Product can be listed in several Shops and a Shop can contain many different Products, so I have these relationship.ini files:

Products:
[Shops]
Shops.ID = ShopProducts.ShopID
ShopProducts.ProductID = "$ID"

Shops:
[Products]
Products.ID = ShopProducts.ProductID
ShopProducts.ShopID = "$ID"

Basically, this works for me, but I have several questions and difficulties that I could not find in the manuals - sorry, but I'm just a newbie to xataface and I'm not native English, perhaps you can help.

What I want to achieve: I select a specific product. Under the "Shops" menu, I can select all the shop names where this product will be listed (not the ID but the actual short name of the shop is shown). Either from a select list (with CTRL-key I can select multiple shops) or with a comma-separated text field, I won't mind.

What goes wrong:
1. I select a product and click on "Shops" and "add new record".
I get a select list of all the available shops (just the Shop ID is shown) and a text field "ShopID: ___" below that. The selection from the list is not entered into the field, but I have to manually type the shop ID into the text field to make this work...
I would like to select multiple stores for this product from the select list and just post this to the database.

2. After adding one of the Shops to this Product relationship, a list with all the join fields is shown:
ID Name DescriptionShort ShopID ProductID

I don't want to confuse the user and just show the shop's name (DescriptionShort) in the list of shops for this product, nothing more. How can I disable certain fields to become visible in this list?

Thanks for any hints - I'll certainly need to dig a bit more into the docs ;-)

Bernd
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby shannah » Sun Jul 05, 2009 11:01 am

1. I select a product and click on "Shops" and "add new record".
I get a select list of all the available shops (just the Shop ID is shown) and a text field "ShopID: ___" below that. The selection from the list is not entered into the field, but I have to manually type the shop ID into the text field to make this work..


Strange. I have set up what I think is an identical scenario to yours to test and it seems to work correctly (i.e. the shop list contains the shop title, and there is no shop id field. Can you post the table definitions in question so I can replicate it with more exactness.

I would like to select multiple stores for this product from the select list and just post this to the database


Currently xataface doesn't provide this sort of interface. One thing that might be close is to set:
Code: Select all
action:delegate=related_records_checkboxes


Place this in the relationship definition in the relationships.ini file.

Which would cause the "Shops" tab of a product to simply contain checkboxes with the available shops. This is only feasible when the number of shops is small. I have been doing a lot of work with jquery lately and am thinking about doing a makeover of the relationships interface if I get a chance. The ability to more easily add multiple records at a time to the relationship would be a good improvement.

I don't want to confuse the user and just show the shop's name (DescriptionShort) in the list of shops for this product, nothing more. How can I disable certain fields to become visible in this list?


You can use the visibility:columnname directive in your relationship definition.

e.g.
Code: Select all

[Shops]
    ...
    visibility:ID=hidden



The relationships.ini wiki page is a little bare at this point, but :
http://xataface.com/wiki/relationships.ini_file

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Postby linux123 » Mon Jul 06, 2009 10:40 am

shannah wrote:
1. I select a product and click on "Shops" and "add new record".
I get a select list of all the available shops (just the Shop ID is shown) and a text field "ShopID: ___" below that. The selection from the list is not entered into the field, but I have to manually type the shop ID into the text field to make this work..


Strange. I have set up what I think is an identical scenario to yours to test and it seems to work correctly (i.e. the shop list contains the shop title, and there is no shop id field. Can you post the table definitions in question so I can replicate it with more exactness.


I guess I must have done something stupid - I just can't see the bug.
These are the tables:

Code: Select all
CREATE TABLE `Shops` (
  `ID` varchar(24) NOT NULL,
  `Name` varchar(128) NOT NULL,
  `DescriptionShort` varchar(255) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ShopProducts` (
  `ShopID` varchar(2) NOT NULL,
  `ProductID` varchar(24) NOT NULL,
  `PriceNet` decimal(9,2) NOT NULL,
  `PriceBrut` decimal(9,2) NOT NULL,
  PRIMARY KEY  (`ShopID`,`ProductID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `Products` (
  `ID` varchar(24) NOT NULL,
  `Name` varchar(128) NOT NULL,
  `SKU` varchar(24) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `SKU` (`SKU`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Currently xataface doesn't provide this sort of interface. One thing that might be close is to set:
Code: Select all
action:delegate=related_records_checkboxes


If I add this to relationship.ini, the link "Add existing element" from the Shops tab within Products disappears....

This is my current relationship.ini:
Code: Select all
[Shops]
Shops.ID = ShopProducts.ShopID
ShopProducts.ProductID = "$ID"
action:visible=1
section:visible=0
action:condition=0
actions:addexisting=1
actions:addnew=0
#action:delegate=related_records_checkboxes
action:label="Shops"



Which would cause the "Shops" tab of a product to simply contain checkboxes with the available shops. This is only feasible when the number of shops is small. I have been doing a lot of work with jquery lately and am thinking about doing a makeover of the relationships interface if I get a chance. The ability to more easily add multiple records at a time to the relationship would be a good improvement.


There are just a few shops - maybe 5 or 10, not more, so if the select list worked, that would be great.

I don't want to confuse the user and just show the shop's name (DescriptionShort) in the list of shops for this product, nothing more. How can I disable certain fields to become visible in this list?


You can use the visibility:columnname directive in your relationship definition.
Yes, I overlooked that, thanks!

Bernd
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby shannah » Mon Jul 06, 2009 10:51 am

Any reason with the Shops.ID column is a VARCHAR(24), but the ShopProducts.ShopID column is a VARCHAR(2)? That could cause some turmoil if the shop ids are longer than 2 chars.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Postby linux123 » Mon Jul 06, 2009 11:46 am

shannah wrote:Any reason with the Shops.ID column is a VARCHAR(24), but the ShopProducts.ShopID column is a VARCHAR(2)? That could cause some turmoil if the shop ids are longer than 2 chars.


I changed Shops.ID to 2 ... nothing changed.
Maybe I should note that I am using Xataface 1.5r2, not the CVS repo version.
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby linux123 » Mon Jul 06, 2009 11:50 am

Maybe I have found something that will shed more light on this issue.
When using "Grid" for Products, the script will bail out with an empty index.php after using up all php session memory - I increased the limit to 64 MByte, so I am rather sure there's some memory problem:
Allowed memory size of 67108864 bytes exhausted (tried to allocate 31 bytes)

The grid works ok for the Shops table though... maybe something in the Products table causes the issues ...

Bernd
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby shannah » Mon Jul 06, 2009 2:38 pm

What versions of mysql, apache, php are you using. I'm going to try to set up a test with your table structure tonight to see if I can reproduce.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Postby linux123 » Tue Jul 07, 2009 1:43 pm

Hi Steve,

this is the configuration:

CentOS 5.3
PHP 5.1.6
Apache 2.2.3
MySQL 5.0.45

Regarding the memory leak when calling Products->grid, I emptied the complete table and still see the same problem. Then I copied the table to Products2 (and made an entry in conf.ini) and the problem was not visible in Products2... it is only visible with the empty "Products" table - this is very strange.

I added the relationships.ini to Products2, and now I get an *empty* drop down list and the remaining required text fields of "Shops" to be filled in.
Probably the empty list is the result from my wrongdoing, if I only knew what it was.

When I enter some data here (existing or new, it doesn't matter), I get this error after pressing "Save":

Fatal error: [pear_error: message="12" code=0 mode=return level=notice prefix="" info="Failed to find table table for field 'Shops.' in Dataface_Table::getTableTableForField() on line 3224 of file /var/www/html/xataface-1.1.5r2/Dataface/Table.php"]On line 3085 of file /var/www/html/xataface-1.1.5r2/Dataface/Table.php in function printStackTrace()
On line 203 of file /var/www/html/xataface-1.1.5r2/Dataface/RelatedRecord.php in function parse(Shops.,)
On line 230 of file /var/www/html/xataface-1.1.5r2/Dataface/RelatedRecord.php in function setValue(,)
On line 109 of file /var/www/html/xataface-1.1.5r2/Dataface/RelatedRecord.php in function setValues(array(,FO,Foo Bar Inc.,No description))
On line 381 of file /var/www/html/xataface-1.1.5r2/Dataface/ExistingRelatedRecordForm.php in function Dataface_RelatedRecord(Dataface_Record Object,Shops,array(,FO,Foo Bar Inc.,No description))
On line of file in function save(array(array(NOSL1110BX-DE),Products2,Shops,existing_related_record,http://localhost/MyDB/ind in /var/www/html/xataface-1.1.5r2/Dataface/Table.php on line 3085

Bernd
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby shannah » Tue Jul 07, 2009 1:48 pm

Thanks for the test case. Xataface is having trouble with the relationship as it is. I have gone through this morning to fix this bug - but it required some core changes and as such it will require a lot of testing before it's ready to roll out.

In the mean time: Is it possible for you to use INT fields for your IDs. And auto-incrementing. Xataface likes these better for relationships.
(Only the Products and Shops tables should be auto-increment ... the join table shouldn't be).

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Postby linux123 » Wed Jul 08, 2009 5:52 am

Hi Steve,

I did as you told me, but I cannot see any change in the behaviour of the application. I still think I did make some mistake somewhere.

Is there a working demo application with relationships available that I could take as a starting base?

I thought from the discussions here that relationships are one of the key concepts of xataface and are quite well-tested by others - this confuses me a lot since I wanted to base some of our central database applications on xataface and I am rather tired of programming PHP manually... so a good and simple solution is what I'm looking for very urgently.

Bernd
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby shannah » Wed Jul 08, 2009 7:00 am

The problem is that this is a very unorthodox table structure. You are using varchar fields for primary keys when generally people would use auto-incrementing INT fields for this purpose. Your join table's foreign keys don't match the types of the primary keys that they reference. The reason why this case hasn't been tested is because it hasn't been used.

This is how I would have defined your tables:

Code: Select all
CREATE TABLE `Shops` (
  `ID` INT(11) NOT NULL auto_increment,
  `Name` varchar(128) NOT NULL,
  `DescriptionShort` varchar(255) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ShopProducts` (
  `ShopID` INT(11),
  `ProductID` INT(11),
  `PriceNet` decimal(9,2) NOT NULL,
  `PriceBrut` decimal(9,2) NOT NULL,
  PRIMARY KEY  (`ShopID`,`ProductID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `Products` (
  `ID` INT(11) NOT NULL auto_increment,
  `Name` varchar(128) NOT NULL,
  `SKU` varchar(24) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `SKU` (`SKU`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



Shops related list

http://dev.weblite.ca/phpimageserver/ph ... _width=600

Add New Shop (Related record)

http://dev.weblite.ca/phpimageserver/ph ... _width=600

Add Existing Shop (Related Record)
http://dev.weblite.ca/phpimageserver/ph ... _width=600

Add Existing Shop with select expanded
http://dev.weblite.ca/phpimageserver/ph ... _width=600

When I finish the changes, it will make the unorthodox table structure work, but it will still be clunkier than this implementation because it need to ask you to manually enter the ID of a shop or product when you enter it. In addition you'll need to explicitly tell xataface that you intent the "Name" field to be used as the title and not the ID field. It not specified, Xataface will just take the first varchar field in the table and use that as the title. In this case your ID fields are varchar. This is why your select list showed only the ID and not the name.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Postby linux123 » Wed Jul 08, 2009 11:50 am

Hi Steve,

thanks a lot for you putting work into this unorthodox thing. I was not aware that alphanumeric primary keys are that unusual, but when I checked several other databases I found that you're quite right - they are all using numeric IDs for some reason I don't know about.

I was already wondering how Xataface should know which was the field used for selecting the relationship, now I know ;-)
Maybe it wouldn't be too bad if there was an additional entry in relationships.ini about this, since I could think that changing the select field later or using a combined field is not too far off in some applications...
just guessing the right field seems a bit esoteric to me :roll:

I already had redefined my DB structure quite as you have shown in the sample SQL above, but for some unknown reason my application won't work as in your screenshots. So something must be different still, but I'm going to find that out tonight or tomorrw. Maybe some caching problem.

One important thing (at least for me). How can I find out the reason this GRID for the products table generates an empty php document. I couldn't find anything in the error_log except this memory problem and I already turned on bug reports: error_reporting(E_ALL); ini_set('display_errors', 'on');

Thanks+Regards
Bernd
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby shannah » Wed Jul 08, 2009 12:16 pm

Try replacing your Dataface/Relationship.php file with the one at
http://weblite.ca/svn/dataface/core/tru ... onship.php

As for the memory leak in grid.. I'm hoping that it has something to do with this relationship issue - as the grid widget does make extensive use of relationships in the background. Was it still giving you the memory problem after you changed your tables to use auto-incrementing integer primary keys?

On a side note:
Nothing wrong with using varchar primary keys. However if the key is just going to be some numeric id than it makes much more sense usually to do it as an int auto_incrementing. In general it makes like easier almost always to use integer primary keys because it makes a sort of permanent identifier for the record that is independent of its content. Additional indexes and keys can be added to other columns for search performance.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Postby linux123 » Fri Jul 10, 2009 9:34 am

Hi Steve,

with the help of your updated code, the relationships now work as expected, thanks a lot!

For the grid, yes - the Products grid still has this memory leak and I'm not sure what it is. If I rename to Products2, the memory leak disappears mysteriously...

Regarding the alphanumeric index question. There are some more historic reasons why we don't use integer indices most of the time.
When programming a webshop, usually items are referenced with something like "index.php?item=1234". This webshop URL used to be indexed by seach engines like Google. When we emptied the products database and reimported, all items usually got a new index, so next time SE came around, everything was mixed up, resulting in bad or non-existent page ranks.
Same problem for referencing related pages like downloads or screenshot links. Now that most webshops have SEO, it is no longer a problem. But I still consider auto-incrementing integer indices as additional fields that don't fulfill a real purpose, since e.g. a product SKU must always be unique anyways. But even that doesn't matter that much any more since memory is cheap and an Integer won't make the difference nowadays ;-)

Bernd
linux123
 
Posts: 25
Joined: Tue Feb 10, 2009 1:00 pm

Postby shannah » Fri Jul 10, 2009 10:00 am

When you renamed to Products2, did you also rename the configuration folder/delegate class etc.. to Products2? It could be something in the configuration files. I would try naming it back to Products and comment out your relationships in the relationships.ini file to see if that makes a diff (to try to narrow it down).

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Next

Return to Xataface Users

Who is online

Users browsing this forum: Majestic-12 [Bot] and 1 guest

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved