CODEWITHSUNDEEP
phpmysqlsqllaravel
Anuar Alfetahe
Anuar Alfetahe

Reputation: 79

Laravel sql if statement

I want to display all products on my page. Each product has x number of images. Some products have images which are set to be cover images. Now I need to get all products with the cover images, and if a particular product doesn't have a cover image set, then I need to get a random image.

+----+--------------+
| id | product_name | 
+----+--------------+
|  1 | Ferrari      |  
|  2 | Audi         |  
|  3 | BMW          | 
+----+--------------+

+----+--------------+------------+-------------+--+
| id |  image_name  | product_id | cover_image | 
+----+--------------+------------+-------------+
| 1  | audi.jpg     |          2 |           1 |  
| 2  | Audis.jpeg   |          2 |             |  
| 3  | bemms.jpg    |          3 |           1 |  
| 4  | ferrari.jpeg |          1 |             |  
| 5  | ferr.jpg     |          1 |             |  
+----+--------------+------------+-------------+

So far I've tryed this code but it doesn't solve my problem because I only get pictures with the cover image set.

public static function get_all_products() {
  return \DB::table('products')
      ->leftjoin('product_pictures', 'products.id', '=', 'product_pictures.product_id')
      ->select('products.name', 'product_pictures.images_name')
      ->where('product_pictures.cover_image', '=', 1)
      ->get();
}

Any help is welcome!

Upvotes: 2

Views: 102

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You could do a self join to product_pictures table with a slight tweak in on clause 

select p.*, pp1.*
from products p
join product_pictures pp1 on p.id = pp1.product_id
left join product_pictures pp2 
      on  pp1.product_id =  pp2.product_id
      and case when pp1.cover_image is null
               then pp1.id > pp2.id
               else null 
          end
where pp2.product_id is null
order by p.id

Demo

Upvotes: 2

Anuar Alfetahe
Anuar Alfetahe

Reputation: 79

Im going to answer my own question maybe somebody will face the same problem. The sql query i actually need:

select id, name, images_name from (
(select `products`.`id`, `products`.`name`,  `product_pictures`.`cover_image`, `product_pictures`.`image_name` 
from `products` 
left join `product_pictures` on `products`.`id` = `product_pictures`.`product_id` 
where `product_pictures`.`cover_image` = 1 OR `product_pictures`.`cover_image` IS NULL
group by `products`.`id`) 

UNION

select id, name, images_name from (
(select `products`.`id`, `products`.`name`,  `product_pictures`.`cover_image`, `product_pictures`.`image_name` 
from `products` 
left join `product_pictures` on `products`.`id` = `product_pictures`.`product_id` 
where `product_pictures`.`cover_image` = 0
group by `products`.`id`) 

    ) t
group by id

Upvotes: 0

Related Questions