laravel select one column from joined table

Question

i am stuck with eloquent and laravel.

any help would be appriciated.

setup:

posts_table

id | name | desc...

photos_table

id | post_id | image_path

posts model

public function photo()
    {
        return $this->hasOne('App\Photo');
    }

photos model

public function posts()
{
return $this->belongsTo('App\Flyer');
}

i want to fetch all posts and only one image that belongs to each post, because there can be more images per post. cant create this with eloquent or db builder.

edit: one more q :)

and how to get only desired columns, like posts_table.name and photos_table.image_path

Possible duplicate of [Laravel - Limit each child item efficiently](http://stackoverflow.com/questions/26247467/laravel-limit-each-child-item-efficiently) — Joseph Silber, Oct 11 '15 at 21:08
Why does the relation from `photo` model belongs to `flyer`? Shouldn't that be `post`? Also, if a post can have more than one photo your should use `hasMany` not `hasOne` in the `post` model. — Björn, Oct 11 '15 at 21:43
Can you update your question with more info from the model files, like filenames and classnames? — Björn, Oct 11 '15 at 21:44

score 0 · Answer 1 · answered Oct 12 '15 at 09:28

For The hassle free use of Eloquent ORM, Please make your model name in singular form like posts make it post.Then rename column id to post_id from database. Do it for others. Now look at your model.

addprotected $primaryKey="post_id"; Do it for others. It will take your headache to joining table using hasOne() orhasMany() others method.It will generate primary key and foreign key as(modelname_id) for post model post_id for relation. Now write your relation to get photos with post in post model

public function photos(){
    return $this->hasMany('App\Photo');

}

Relation done :)

In your controller to get post with photo,you have to write

$posts=Post::with('photos')->get();

print_r($posts);exit;//check is it working

It will gives you all photo of each post you have to use a nested loop to view them one or more. as if($posts->photos){ foreach($posts->photos as $sn=>$photo) } Please try this concept,It may help you :D

i shall try it as soon i get home, tnx, and how to get only one photo per post? — pinarella, Oct 12 '15 at 12:35

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

from this post i found an answer: How to select just 1 child table item for each parent record?

and i had to do it in raw sql, maybe refactoring to eloquent will be the next task.

SELECT posts.id, image_path 
  FROM (SELECT * FROM posts) posts 
  LEFT JOIN (SELECT * FROM photos_table GROUP BY posts_id) photos_table 
  ON  posts.id = posts_id

edited May 23 '17 at 12:22

Community

1
1

answered Oct 13 '15 at 21:45

pinarella

805
8
16

laravel select one column from joined table

2 Answers2