CODEWITHSUNDEEP
phpjoinsymfony1criteriapropel
Andreu Ramos
Andreu Ramos

Reputation: 2908

Criteria Bidirectional Join?

I'm working on a symfony1.4 project, with propel ORM. In my model, I have a table where its elements can be linked with many elements of the same table, by another table (link table). Some code will explain better:

table1:
    id_element: integer;
    [...]

and the link table:

 link_table:
     id1: fk to table1;
     id2: fk to table1;

I need to build a query with Propel Criteria that returns me all the related elements with an specific element. The problem is that the element I want to specify, can be as in id1 field as in id2 field of the link table.

now some of my criteria definition code (not working obviously)

$c = new Criteria();
$c->addJoin($linkTable::ID1,$table::ID);
$c->addJoin($linkTable::ID2,$table::ID);
$c->addOr($linkTable::ID1,$specific_id);
$c->addOr($linkTable::ID2,$specific_id);
$result = $table->doSelect($c);

and this is a SQL like that I want to generate:

SELECT * FROM table
    WHERE table.ID IN
        (SELECT link_table.ID1 FROM link_table
            WHERE link_table.ID2 = "the id that I want"
        )
    OR table.ID IN 
        (SELECT link_table.ID2 FROM link_table
            WHERE link_table.ID1 = "the id that I want"
        )

So must I do 2 joins, one for each side of the link table? is there a way to do an "or-join"?

Upvotes: 1

Views: 221

Answers (4)

Andreu Ramos
Andreu Ramos

Reputation: 2908

at last i found a way to do it using criteria:

$c = new Criteria();
    $q1 = new Criteria();
    $q1->add($linkPeer::ID1,$my_value);
    result1 = $linkPeer->doSelect($q1);
    foreach($result1 as $result){
        ids1[] = $result->getID();
    }
    $q2 = new Criteria();
    $q2->add($linkPeer::ID2,$my_value);
    result2 = $linkPeer->doSelect($q2);
    foreach($result2 as $result){
        ids2[] = $result->getID();
    }
$ids = array_merge($ids1,$ids2);
$c->add($tablePeer::ID,$ids,Criteria::IN);
$totalResult = $tablePeer->doSelect($c);

maybe not the best way but working fine.

Thank you very much for your answers!!

Upvotes: 0

William Durand
William Durand

Reputation: 5519

Avoid the use of Criteria, as halfer said, it's better to use the ModelCriteria and its API. Read the following doc for more information: http://www.propelorm.org/reference/model-criteria.html

Upvotes: 0

halfer
halfer

Reputation: 20420

Just thinking out aloud - that looks functionally identical to this:

SELECT
    *
FROM
    my_table
LEFT JOIN
    link_table lt1 ON (lt1.id1 = my_table.id)
LEFT JOIN
    link_table lt2 ON (lt2.id2 = my_table.id)
WHERE
    lt1.id2 = X
OR
    lt2.id1 = X

If that is identical (obviously do check) then that should be much easier to build a query for. Also, try to use ModelCriteria if you can - I suspect Criteria will be deprecated in Propel 2, and it is a good idea to plan ahead for that.

Upvotes: 0

macgyver
macgyver

Reputation: 1279

Maybe I don't well understand what you need... you have two foreign keys of a table that are both linked to the same table, and so id, and you want an or-join. Why not split in two criteria and so two selections?

Upvotes: 0

Related Questions