5

I read through lithium\data\model\query, but I didn't see any examples of joins.

ton.yeung
  • 4,793
  • 6
  • 41
  • 72
  • 1
    did you look at the test cases? maybe there are some... – Tomen Feb 22 '12 at 09:55
  • @Tomen When I wrote the question, no, thanks for the suggestion. I just checked it out, but I'm not really sure what the test is doing? What are foo, bar, and baz supposed to be? – ton.yeung Feb 22 '12 at 10:56

2 Answers2

5

There are multiple ways to perform a join with Lithium.

  1. Lithium will handle joins for you where you have defined relationships (examples in the manual).
  2. You can add joins to an existing Query object using the join() method (see the API)).
  3. You can pass an array of Query objects to finders using the key joins.
  4. You can pass the SQL directly to a connection using Connection->read().

The other methods are reasonably well documented, so I'll give an example of passing Query objects to a finder.

$fields = array('id', 'name', 'slug');                                                                                                                                                                                                  
$joins = array();
$joins[] = new Query(array(
  'source' => 'client_tests',  
  'constraint' => array('Test.id' => 'client_tests.test_id'),
));
$conditions['client_id'] = $this->data['client_id'];
$tests = Test::all(array(
   'conditions' => $conditions,
   'fields' => $fields,
   'joins' => $joins
));

The source is the table that you want to join and constraint is the join criteria. Lithium aliases the find table to the name of the model, so use that in your constraint. You can then pass the joins in to any finder along with any other parameters you want.

Note that at the time of writing, joins (and relationships) will only work with a relational database, not for things like MongoDB's DBRef.

Update: Removed links that have been link jacked.

Ramin Firooz
  • 506
  • 8
  • 25
michaeltwofish
  • 4,096
  • 3
  • 28
  • 32
  • Hi, sorry to necro this, my query is failing on the field names. For example, if slug was in both tables, one with a capital S, one with a lowercase s, the lowercase s can get specified through table.slug, but the uppercase S only shows table2. IE: "SELECT table.slug, table" instead of "SELECT table.slug, table2.slug". I'm guess this has to do with human translation function stuff lithium has? – ton.yeung Apr 10 '12 at 02:35
  • This sounds like a lithium bug. Check the lithium github issues, and if it doesn't exist yet, open an issue and attach a failing test case. – michaeltwofish Apr 11 '12 at 08:08
  • Your API link in item #2 has been link jacked by some SEO company (EDIT: Both links actually). – mopsyd Jan 27 '16 at 22:54
  • Thanks mopsyd, I've removed the links and become marginally sadder at the internet. – michaeltwofish Feb 08 '16 at 01:49
1

If you set up a relationship (using hasOne, hasMany, or belongsTo) you can fetch the related data by supplying a with key in the options of your find call.

Like this:

$categories = Categories::find('all', array(
  'with' => 'Products'
));

Check out the manual chapter on Relationships for more detail.

Ramin Firooz
  • 506
  • 8
  • 25
benzado
  • 82,288
  • 22
  • 110
  • 138