Get Specific Columns Using With Function in Laravel Eloquent

Question

I have two tables  User and Post  One User can have many posts and one post belongs to only one user    In my User model I have a hasMany relation     public function post        return  this- gt hasmany  post        And in my post model I have a belongsTo relation     public function user        return  this- gt belongsTo  user        Now I want to join these two tables using Eloquent with   but want specific columns from the second table  I know I can use the Query Builder but I don t want to    When in the Post model I write     public function getAllPosts         return Post  with  user  - gt get        It runs the following queries     select   from  posts  select   from  users  where  users   id  in   lt 1 gt    lt 2 gt     But what I want is     select   from  posts  select id username from  users  where  users   id  in   lt 1 gt    lt 2 gt     When I use     Post  with  user  - gt get array  columns           It only returns the column from the first table  I want specific columns using with   from the second table  How can I do that

User · Answer

In Laravel 5 7 you can call specific field like this   users   App Book  with  author id name  - gt get      It is important to add foreign key field in the selection

User · Answer

Well I found the solution  It can be done one by passing a closure function in with   as second index of array like Post  query       - gt with array  user    gt  function  query             query- gt select  id   username                - gt get     It will only select id and username from other table  I hope this will help others   Remember that the primary key  id in this case  needs to be the first param in the  query- gt select   to actually retrieve the necessary results

User · Answer

Note that if you only need one column from the table then using  lists  is quite nice  In my case i am retrieving a user s favourite articles but i only want the article id s    favourites    user- gt favourites- gt lists  id      Returns an array of ids  eg   Array        0    gt  3      1    gt  7      2    gt  8

User · Answer

When going the other way  hasMany    User  with array  post   gt function  query        query- gt select  id   user id       - gt get      Don t forget to include the foreign key  assuming it is user id in this example  to resolve the relationship  otherwise you ll get zero results for your relation

User · Answer

If you use PHP 7 4 or later you can also do it using arrow function so it looks cleaner  Post  with   user    gt  fn   query    gt   query- gt select  id   username    - gt get

User · Answer

EmployeeGatePassStatus  with  user id name  - gt get

User · Answer

I came across this issue but with a second layer of related objects   Awais Qarni s answer holds up with the inclusion of the appropriate foreign key in the nested select statement  Just as an id is required in the first nested select statement to reference the related model  the foreign key is required to reference the second degree of related models  in this example the Company model   Post  with   user    gt  function   query             query- gt select  id   company id    username            user company    gt  function   query             query- gt select  id    name           - gt get      Additionally  if you want to select specific columns from the Post model you would need to include the user id column in the select statement in order to reference it   Post  with   user    gt  function   query             query- gt select  id    username                - gt select  title    content    user id       - gt get

User · Answer

You can also specify columns on related model at the time of accessing it   Post  first  - gt user  - gt get   columns

User · Answer

If you want to get specific columns using with   in laravel eloquent then you can use code as below which is originally answered by  Adam in his answer here in response of this same question    Post  with  user id username  - gt get      So i have used it in my code but it was giving me error of 1052  Column  id  in field list is ambiguous  so if you guys are also facing same problem  Then for solving it you have to specify table name before the id column in with   method as below code   Post  with  user user id username  - gt get

User · Answer

So  similar to other solutions here is mine     For example you have this relation defined with  quot user   quot  method public function user         return  this- gt belongsTo  User         Just make another one defined with  quot user frontend   quot  method public function user frontend         return  this- gt belongsTo  User  - gt select array  id    username           Then use it later like this  thing   new Thing     thing- gt with  user frontend        This way  you get only id and username      and if you want all fields you can do this   thing   new Thing     thing- gt with  user

User · Answer

In your Post model   public function userWithName         return  this- gt belongsTo  User  - gt select array  id    first name    last name         Now you can use  post- gt userWithName

User · Answer

For loading models with specific column  though not eager loading  you could  In your Post model public function user         return  this- gt belongsTo  User  - gt select array  id    username        Original credit goes to Laravel Eager Loading - Load only specific columns

User · Answer

You can try this code   It is tested in laravel 6 version    Controller code  public function getSection Request  request       Section  with   sectionType    gt  function  q           q- gt select  id    name         - gt where  position  1 - gt orderBy  serial no    asc  - gt get   id   name      description       return response  - gt json  getSection       Model code public function sectionType        return  this- gt belongsTo Section Type  class   type id

User · Answer

You can do it like this since Laravel 5 5    Post  with  user id username  - gt get      Care for the id field and foreign keys as stated in the docs      When using this feature  you should always include the id column and   any relevant foreign key columns in the list of columns you wish to   retrieve    For example  if the user belongs to a team and has a team id as a foreign key column  then  post- gt user- gt team is empty if you don t specifiy  team id   Post  with  user id username team id  - gt get

User · Answer

Try with conditions    id   1  Post  with array  user   gt function  query  use   id        query- gt where  id       id        query- gt select  id   username       - gt get

User · Answer

Now you can use the pluckmethod on a Collection instance     This will return only the uuid attribute of the Post model  App Models User  find 2 - gt posts- gt pluck  uuid     gt  Illuminate Support Collection   983      all            1           2           3

[php] Get Specific Columns Using “With()” Function in Laravel Eloquent

Examples related to php

Examples related to laravel

Examples related to orm

Examples related to eloquent

Examples related to laravel-query-builder