CODEWITHSUNDEEP
phpmysqljsonlaravel
z0mbieKale
z0mbieKale

Reputation: 1028

How to collect items in Laravel eloquent query?

I am trying to fetch all profile data for user. And user can have multiple files uploaded for the server which can give the user badges. I have 2 users in my test environment and 1 of the users has 3 files. Now when I return JSON from the controller I get three results, but I would like to merge or somehow combine so the one user could have all data in one object.

Here is the JSON I currently get returned

[
    {
        "id":1,
        "avatar":"john.jpg",
        "role":2,
        "first_name":"John",
        "last_name":"Doe",
        "user_name":"johndoe",
        "account_status":2,
        "one_child":"6",
        "introduction":"test",
        "file_type":1,
        "file":"1498725633.docx",
        "status":2,
        "lat":59.44,
        "lng":24.74,
        "rating":"5",
        "recommended":"1",
        "rating_count":2,
        "distance":0.29988841983648
    },
    {
        "id":1,
        "avatar":"john.jpg",
        "role":2,
        "first_name":"Joe",
        "last_name":"Doe",
        "user_name":"johndoe",
        "account_status":2,
        "one_child":"6",
        "introduction":"test",
        "file_type":4,
        "file":"118771941-merged.mp4",
        "status":1,
        "lat":59.44,
        "lng":24.74,
        "rating":"5",
        "recommended":"1",
        "rating_count":2,
        "distance":0.29988841983648
    },
    {
        "id":4,
        "avatar":"capture.JPG",
        "role":2,
        "first_name":"Jane",
        "last_name":"Doe",
        "user_name":"janedoe",
        "account_status":2,
        "one_child":"4",
        "introduction":"Test",
        "file_type":2,
        "file":"1498732136.docx",
        "status":1,
        "lat":59.46,
        "lng":24.83,
        "rating":"3",
        "recommended":"2",
        "rating_count":2,
        "distance":5.5651349391591
    }
]

As you can see john doe has two files but I would like to have all the files and file types under one object so there would be only one John and one Jane object atm.

Here is my raw sql extracted from laravel

select
   `users`.`id` as `id`,
   `users`.`avatar`,
   `users`.`role`,
   `users`.`first_name`,
   `users`.`last_name`,
   `users`.`user_name`,
   `users`.`account_status`,
   `user_wage_preferences`.`one_child`,
   `user_introductions`.`introduction`,
   `user_files`.`file_type`,
   `user_files`.`file`,
   `user_files`.`status` as `status`,
   `user_contact_informations`.`lat`,
   `user_contact_informations`.`lng`,
   ROUND(AVG( user_reviews.rating )) AS rating,
   SUM(user_reviews.recommended) as recommended,
   COUNT(user_reviews.id) as rating_count,
   `user_files`.`status`,
   (
      6371 * acos( cos( radians(59.4424504) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(24.7377842) ) + sin( radians(59.4424504) ) * sin( radians(lat) ) ) 
   )
   AS distance 
from
   `users` 
   inner join
      `user_files` 
      on `users`.`id` = `user_files`.`user_id` 
   left join
      `user_reviews` 
      on `users`.`id` = `user_reviews`.`nanny_id` 
   inner join
      `user_introductions` 
      on `users`.`id` = `user_introductions`.`user_id` 
   inner join
      `user_wage_preferences` 
      on `users`.`id` = `user_wage_preferences`.`user_id` 
   inner join
      `user_contact_informations` 
      on `users`.`id` = `user_contact_informations`.`user_id` 
where
   `users`.`role` = ? 
   and `users`.`account_status` = ? 
group by
   `users`.`id`,
   `users`.`avatar`,
   `users`.`role`,
   `users`.`first_name`,
   `users`.`last_name`,
   `users`.`user_name`,
   `user_files`.`status`,
   `users`.`id`,
   `user_contact_informations`.`lat`,
   `user_contact_informations`.`lng`,
   `user_wage_preferences`.`one_child`,
   `user_introductions`.`introduction`,
   `user_files`.`file_type`,
   `user_files`.`file` 
having
   `recommended` > ? 
order by
   `distance` asc jquery - 3.2.1.min.js:2 Uncaught TypeError: Cannot use 'in' operator to search for 'length' in 
   select
      `users`.`id` as `id`,
      `users`.`avatar`,
      `users`.`role`,
      `users`.`first_name`,
      `users`.`last_name`,
      `users`.`user_name`,
      `users`.`account_status`,
      `user_wage_preferences`.`one_child`,
      `user_introductions`.`introduction`,
      `user_files`.`file_type`,
      `user_files`.`file`,
      `user_files`.`status` as `status`,
      `user_contact_informations`.`lat`,
      `user_contact_informations`.`lng`,
      ROUND(AVG( user_reviews.rating )) AS rating,
      SUM(user_reviews.recommended) as recommended,
      COUNT(user_reviews.id) as rating_count,
      `user_files`.`status`,
      (
         6371 * acos( cos( radians(59.4424504) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(24.7377842) ) + sin( radians(59.4424504) ) * sin( radians(lat) ) ) 
      )
      AS distance 
   from
      `users` 
      inner join
         `user_files` 
         on `users`.`id` = `user_files`.`user_id` 
      left join
         `user_reviews` 
         on `users`.`id` = `user_reviews`.`nanny_id` 
      inner join
         `user_introductions` 
         on `users`.`id` = `user_introductions`.`user_id` 
      inner join
         `user_wage_preferences` 
         on `users`.`id` = `user_wage_preferences`.`user_id` 
      inner join
         `user_contact_informations` 
         on `users`.`id` = `user_contact_informations`.`user_id` 
   where
      `users`.`role` = ? 
      and `users`.`account_status` = ? 
   group by
      `users`.`id`,
      `users`.`avatar`,
      `users`.`role`,
      `users`.`first_name`,
      `users`.`last_name`,
      `users`.`user_name`,
      `user_files`.`status`,
      `users`.`id`,
      `user_contact_informations`.`lat`,
      `user_contact_informations`.`lng`,
      `user_wage_preferences`.`one_child`,
      `user_introductions`.`introduction`,
      `user_files`.`file_type`,
      `user_files`.`file` 
   having
      `recommended` > ? 
   order by
      `distance` asc

I have all the connection made in my migrations so user_id references id in users table. How could I fetch all the files under one user object?

Upvotes: 0

Views: 93

Answers (1)

Teun
Teun

Reputation: 926

Instead of joining the users and user_files tables in the query you can add a relation to them.

In your case to the users model would have a function something like this:

public function files()
{
    return $this->hasMany('App\UserFiles', 'user_id'); 
    //Where UserFiles is the name of the model of the user_files table
    //and user_id is the foreign key
}

Once you have this relation set up you can add the files relation into your query by using the with:

Users::with('files')->where(...)->orderBy(...)->get();

This will load the files into the user objects like so:

 {
    "id":1,
    "avatar":"john.jpg",
    "role":2,
    "first_name":"John",
    "last_name":"Doe",
    "user_name":"johndoe",
    "account_status":2,
    "one_child":"6",
    "introduction":"test",
    "files": [
        {
            //File Object
        },
        {
            //File Object
        }
    ],
    "lat":59.44,
    "lng":24.74,
    "rating":"5",
    "recommended":"1",
    "rating_count":2,
    "distance":0.29988841983648
}

Upvotes: 3

Related Questions