Lithium with left join

Question

I'm new with Lithium.I need to do a left join.This is the query I need.

SELECT `title`,`body`,`user_id`,`users`.`username`,`users`.`id` 
FROM `posts` LEFT JOIN `users` 
        ON `user_id` = `users`.`id`

I try to do something like this.

  $conditions['user_id'] = $this->data['user_id'];
                $posts = Post::all(array(
                  'conditions' => $conditions,
                  'fields' => array('title','body','username'),
                  'join' => array('source' => 'posts', 
                  'type' => 'LEFT',
                  'constraint' => array('post.user_id' => 'Users.id')), 
                     ))->data();

score 1 · Answer 1 · edited May 23 '17 at 12:08

There is a great tutorial (using a blog as an example) here which explains using Relationships in Lithium, Relationships make using relational data and JOINS simpler.

You can also check out the official docs as well as this answer: How do I perform joins with lithium models?

Once you have your Relationships set on your Models you can do your JOINS as simply as:

$posts = Posts::find('all', array(
    'with' => 'Users'
));

There are a couple differant types of Relationships in Lithium, which roughly corrspond to differant types of JOINS ...

hasOne: the current object is linked to a single object of another type
hasMany: the current object is linked to many objects of another type
belongsTo: the current object is owned and marked as related to another object

Lithium with left join

1 Answers1