Get all ads data with ads images in one query-CodePudding

1st table name my_ads and entries

 ---- ------------- -------- ---------- --------- 
| id | title       | gender | country  | user_id |
 ---- ------------- -------- ---------- --------- 
| 35 | NOman Javed | male   | Pakistan | 1       |
| 34 | Noman Javed | male   | Pakistan | 1       |
| 33 | Noman Javed | male   | Pakistan | 1       |
| 32 | Noman Javed | male   | Pakistan | 1       |
| 31 | Noman Javed | male   | Pakistan | 1       |
 ---- ------------- -------- ---------- ---------

2nd table ads_images

 ---- ----------- --------------------------------- 
| id | my_ads_id | image_path                      |
 ---- ----------- --------------------------------- 
| 28 | 35        | 1645180564-Screenshot-(529).png |
| 27 | 35        | 1645180562-Screenshot-(528).png |
| 26 | 35        | 1645180558-Screenshot-(527).png |
| 25 | 34        | 1645180318-Screenshot-(529).png |
| 24 | 34        | 1645180316-Screenshot-(528).png |
 ---- ----------- ---------------------------------

I had written the query and combined it in one array value but I want it to be done with one query.

$all_ads = DB::table('my_ads')->get();

$my_ads_images = DB::table('ads_images')->select('id','my_ads_id', 'image_path')- 
>groupBy('my_ads_id')->get();

then compile with both tables values in one array on sub-index

foreach($all_ads as $ads_key => $ads) {

    $my_ads_array[$ads_key]['id'] = $ads->id;
    $my_ads_array[$ads_key]['title'] = $ads->title;

    foreach($my_ads_images as $my_ads_image) {
        if($ads->id == $my_ads_image->my_ads_id) {
            $my_ads_array[$ads_key]['image_path'] = $my_ads_image->image_path;
        }
    }
}

Can I write query to achieve $my_ads_array[$ads_key]['image_path'] = array of images here with one query. I am using Laravel 8 with MySQL.

I know it's a basic query but I don't know how it will work. I tried joins but that didn't work for me don't know why.

Looking for output like this:

[0] => Array
    (
        [id] => 35
        [title] => Noman Javed
        [gender] => male            
        [description] => Height: 5.6''
        [country] => Pakistan
        [image_path] => Array
            (
                [0] => 1645180558-Screenshot-(527).png
                [1] => 1645180562-Screenshot-(528).png
                [2] => 1645180564-Screenshot-(529).png
            )

        [created_at] => 2022-02-18 10:35:49

    )

CodePudding user response：

You can't take response in array format in query result but you can get that response seperated by comma. Try following code

SELECT m.*, GROUP_CONCAT(a.image_path SEPARATOR ', ') FROM my_ads m inner join ads_images a  on m.id=a.my_ads_id group by m.id;

When you get response then put loop on that and explode this

a.image_path

with comma and you will get the array.

CodePudding user response：

You need to add a JOIN (basic explanation https://www.w3schools.com/sql/sql_join.asp) to

$my_ads_images = DB::table('ads_images')->select('id','my_ads_id', 'image_path')- 
>groupBy('my_ads_id')->get();

like so:

$my_ads_images = DB::table('ads_images')->join('my_ads', 'my_ads.id', '=', 'ads_images.my_ads_id')->select('id','my_ads_id', 'image_path')->groupBy('my_ads_id')->get();

the syntax may differ slightly as you didn't specify what exactly are you using for building the query but this should give you the rough idea of what to do

CodePudding user response：

I converted MySQL query of @Shaniawan into laravel syntax and placed left join in the query that returns all of entries of my_ads and only ads_images that have images.

$my_ads_and_images = DB::table('my_ads')
    ->leftJoin('ads_images', 'my_ads.id', '=', 'ads_images.my_ads_id')
    ->select('my_ads.*', DB::raw('group_concat(ads_images.image_path) as image_path'))
    ->groupBy('my_ads.id')
    ->paginate(12);

Hope this will help someone in the future.