Laravel sql if statement

Question

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!

Give the models a go. It's far easier to fetch data with them. — Dan
– Dan, Commented May 23, 2018 at 17:23

M Khalid Junaid · Accepted Answer · 2018-05-24 06:02:44Z

2

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

answered May 24, 2018 at 6:02

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anuar Alfetahe Over a year ago

Thanks, its looking better!

Anuar Alfetahe · Accepted Answer · 2018-05-23 19:25:06Z

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

Collectives™ on Stack Overflow

Laravel sql if statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related