Re: DB_DataObjects: Dont know how to multi join

Patrick Schuster wrote:

Hi all, I've 3 tables, locations (id, name, ...) categories (id, name, ...) loc_cat_xref (fk_loc_id, fk_cat_id) As I want to have more than one category for one location and vice versa I've created the 3rd table "loc_cat_xref" which contains the keys of the "locations" and "categories" table. I've setup the "*.links.ini"-File in this way: [loc_cat_xref] fk_loc_id = locations:id fk_cat_id = categories:id Now I want to have something like this:

SELECT * FROM locations AS loc INNER JOIN loc_cat_xref AS lcx ON lcx.fk_loc_id = loc.id INNER JOIN categories AS cat ON lcx.fk_cat_id = cat.id

$loc = new DataObjects_Locations; $lcx = new DataObjects_loc_cat_xref; $cat = new DataObjects_categories; $loc->joinAdd($lcx,'INNER','lcx'); $cat->joinAdd($loc,'INNER','loc'); $cat->find(); might work.. - it's usually worth experiemting with DebugLevel(1) to see what i makes..

or SELECT * FROM locations AS loc, loc_cat_xref AS lcx, categories AS cat WHERE lcx.fk_loc_id = loc.id AND lcx.fk_cat_id = cat.id

$loc = new DataObjects_Locations; $lcx = new DataObjects_loc_cat_xref; $cat = new DataObjects_categories; $lcx->joinAdd($cat,'','cat'); $loc->joinAdd($lcx,'','lcx'); Theres no support at the moment for doing AS on the base table.. the idea behind mega joins is that if you add a table that alread has a join on it, then the initail join will be added to the new combination.. I have a patch which works the other way round, but it involves storing the list of joined tables in the DataObject, so that it can attempt to work out all the connections.. It may be better to allow this to be added in manually: eg $loc->joinRaw(' AS loc INNER JOIN loc_cat_xref AS lcx ON lcx.fk_loc_id = loc.id INNER JOIN categories AS cat ON lcx.fk_cat_id = cat.id'); I suspect this level of abstraction is to confusing of it's own good. Regards Alan

How can I do this with DataObject?? I've tried to "joinAdd" the tables "locations" and "loc_cat_xref" and this is no problem, but if I want to "joinAdd" "locations" and "categories" I get an error as there is no relation in the "*.links.ini"-File between this tables. I've also downloaded the last CVS and there is a comment, that DataObject now supports "mega join" but I don't know how to use this feature and if this helps me. Thanks in advance for any hints. Bye Patrick