How to get all column data from two tables by using leftJoin in Yii2

user_10516114 0
Viewed 1682

I tried to get books.id and authors.Name in an array using leftJoin() in Yii2.

$data = Book::find()
       ->select("authors.Name, books.id")
       ->leftJoin("authors", "authors.book_id = books.id")
       ->where(['books.id' => '14,16,17,18'])
       ->all();

But this result is only from books table. I can not see authors.Name in this result.

In Laravel, it is easy to get the data like the above. Is it not possible in Yii2?

yii2 sql php
2 Answers

By default all() returns array of related models and usually this models is not able to represent result of such query (for example Book model does not have Name field). Usually it is better to use asArray() - then all() will return list of arrays (which can store any kind of key-value pairs returned by query) instead of AR objects:

$data = Book::find()
    ->select('authors.Name, books.id')
    ->leftJoin('authors', 'authors.book_id = books.id')
    ->where(['books.id' => '14,16,17,18'])
    ->asArray()
    ->all();

foreach ($data as $row) {
    echo "{$data['id']} - {$data['Name']}\n";
}

Or use Query directly:

$data = (new Query())
    ->select('authors.Name, books.id')
    ->from(Book::tableName())
    ->leftJoin('authors', 'authors.book_id = books.id')
    ->where(['books.id' => '14,16,17,18'])
    ->all();

foreach ($data as $row) {
    echo "{$data['id']} - {$data['Name']}\n";
}
user_58124550

In your select clause ->select("authors.Name, books.id") you have not the columns authors.id and then the columns is not retrived from db.

so try adding this column too
and do the fact you have another id referer to the columns using a proper alias eg: autors_id

$data = Book::find()
       ->select("authors.Name, books.id, authors.id as authors_id")
       ->leftJoin(authors, "authors.book_id = books.id")
       ->where(['books.id' => '14,16,17,18'])
       ->all()

and be sure you have the same exact name for authors column name (normally the column name is lowercase ) 

 ->select("authors.name as authors_name, books.id, authors.id as authors_id")

amd be sure you have a correspondant field for receive the select result for author_name and author_id

user_35223120
Related