Select All From Table Article With Table Images But Limit To 1 Image Only

Yii 2.0 General Discussions
1

Hi. I’m fiddling around with Yii2 and I need some help with this query. I have 2 tables; Article and Image. What I would like to achieve is select all from table Article with table Images but limit to 1 image only. Schema as per below:-


Article

----------

- id

- title

- post


Image

----------

- id

- article_id

- thumbnail

- path

Here is my relations:-

models/Image.php


public function getArticle()

{

    return $this->hasOne(Article::className(), ['id' => 'article_id']);

}

models/Article.php


public function getImages()

{

    return $this->hasMany(Image::className(), ['article_id' => 'id']);

}

And here is my query in Article controller


$model = \app\models\Article::find()

         ->with(['images' => function($query) {

                $query->where('image.status = 1');

                $query->limit('1');

                $query->orderBy('created DESC');

          }])

          ->where(['status' => 1]);

The query does not really work as expected. Some record did not return any image even though there is a image record for that particular article ID.

Any help would be greatly appreciated here.

Thank you!

2

Could you please post the generated query?

3

Hi, just got got yii2-debug up and running. Below is the database queries executed:-




SHOW FULL COLUMNS FROM `article`


SHOW FULL COLUMNS FROM `image`


SELECT * FROM `article` WHERE article.1 = :1 LIMIT 5 OFFSET 85


SELECT COUNT(*) FROM `article` WHERE article.1 = :1


SHOW CREATE TABLE `article`


SELECT * FROM `image` WHERE (image.status = 1) AND (`article_id` IN ('86', '87', '88', '89', '90')) ORDER BY `created` DESC LIMIT 1


SHOW CREATE TABLE `image`

Any help is greatly appreciated.

4

Seems the problem is in limit(1)

Here it grabs only 1 image for all articles:


SELECT * FROM `image` WHERE (image.status = 1) AND (`article_id` IN ('86', '87', '88', '89', '90')) ORDER BY `created` DESC LIMIT 1

Try to add also in Article.php




public function getImage()

{

    return $this->hasOne(Image::className(), ['article_id' => 'id']);

}

then




$model = \app\models\Article::find()

         ->with(['image' => function($query) {

                $query->where('image.status = 1');

                $query->orderBy('created DESC');

          }])

          ->where(['status' => 1]);

Check the number of SQL queries, probably it will execute one additional query per article.

5

Hi. I have tried your suggested method. One problem through; it does not limit one image to one article IF an article has more than one image. Is there a way to set LIMIT in relation?

I’ve google-ed around a little bit and found that using GROUP BY SQL query should help. Referenced from here. Here’s my code so far.


$model = \app\models\Article::find()

         ->with(['image' => function($query) {

                $query->where('image.status = 1');

                $query->groupBy('article_id');

                $query->orderBy('created DESC');

          }])

          ->where(['status' => 1]);

It returns 1 image per article eventhough an article have more than 1 image.