We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

mysterious join method's behavior

Hi, I have a question about join method.

For example,

// relationship definition in  Article::initialize()
$this->belongsTo(
    'user_id',
    User::class,
    'id',
    [
        'alias' => 'a',
        'reusable' => false,
    ]
);
$userWithArticles = User::query()->innerJoin(
    Article::class,
    "User.id = a.user_id", // conditions
    "a" // alias
)->execute();

this code executes SQL just like below.

SELECT `users`.`id`, `users`.`name` # articles table's columns are not included!!
FROM `users`
INNER JOIN `articles` AS `a` ON `users`.`id` = `a`.`user_id`

I also would like to fetch from articles table. But, articles table's columns are not included in SELECT.

What should I do to fetch articles table's columns?

Or, please tell me how to solve N+1 problem.

Thank you.



77.7k
edited Aug '20

If you need one article record:

$userWithArticles = User::query()
    ->columns('users.* AS user, articles.* AS article')
    ->innerJoin(
        Article::class,
        "User.id = a.user_id", // conditions
        "a" // alias
    )->execute();

$user = $userWithArticles->user;
$article = $userWithArticles->article;

If you need related article records:

$user = Users::findFirst();
// you named this with the alias option in the belongsTo setup
foreach($user->a as $article) {
    // ...
}


186

I solved by using columns(). Thank you!