Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Where clause with many to many related
  • Hi there.

    I'm drowning into my ORM nested relationship. I have the following models :

    - A model "Things"
    - A model "Locations"
    - And a lot of related model but working, so i wont detail them here

    Things and Location have many to many relationship, with the table through "things_locations" and the through fields, "thing_id" and "location_id'

    I want select some things with a request, but only if they are in the table through, with the given array of allowed location. 

    I ran a lot of test like this :

    if($locations) $things->related('locations', array('where' => array(array('things_locations.location_id', 'IN', $location))));

    // $things is the ORM query

    But nothing work. It always want to create another table alias for the where clause, instead of using the related table (t2_through)

    I don't put the entiere built request, a bit longer and not necessary here.

    Thanks a lot.

  • HarroHarro
    Accepted Answer
    In a standard many-to-many relation, the through table is there for technical reasons (linking the two tables), but is otherwise invisible. As it doesn't have a relation name, you can't access columns of it in your where clause.

    It has an internal alias, which you could use as a workaround if you know it. It is based on the table alias of the child model, with a "_though" suffix. So in a standard related query where you have two tables and a single join, the second table alias will be "t1", so the through table can be accessed using "t1_through.location_id".

    Alternative is to define two relations: one many-to-many, like you have now, and a second one-to-many between "Things" and the through table. You can also define a one-to-many between the through table and "Locations" if you need the entire chain.

    You can then do

    Model_Things::query()->related('thingslocations')->where('thingslocations.locations_id', 'IN', $location)->get();
  • As always you did it right, Harro. So i identified the table, it work with 't3_through'. Theorical question, if the request have to change in the futur, the through 't3' table may change too ? So with this method i have to watch any change to not broke all the thing ?  Or there is a way to watch the internal pointer and get always the same table ?
  • The number is dependent on the number of joins, so t3 means "the third joined related table in this query". So it is variable, and dependent on how you construct the query.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion