Laravel how to join tables and use where condition using Eloquent?

Question

Lets say there are 2 tables:

videos (id, file_name)

matches (id, videos_id, match_number)

I want to get video file_name by match_number. I have read that using eloquent, you can avoid joins.

So I try various things from examples like this:

Video.php

class Video extends Eloquent {

    protected $table = 'videos';

    public function matches()
    {
        return $this->hasMany('Match', 'videos_id');
    }

    /*public function match()
    {
        return $this->belongsTo('Match', 'id');
    }*/

    /**
     * @param $matchNumber
     * @return mixed
     */
    public function getByMatchNumber($matchNumber)
    {
        return $this
            ->matches()
            //->select('videos.file_name')
            //->limit(10)
            ->where('matches.match_number', $matchNumber)
            ->file_name;
             //   ->match()


    }
}

But nothing works.

I think I am assuming well that video hasMany matches because in reality there are many matches which have same video.

So how it should look?

Update

Match_number column is unique, like id. There cannot be multiple matches with same match number. And there is only one video for one match. Limit(10) just was doing to try without where condition, to not get millions of results. But same video is assigned to many matches.

Update:

I remade the function based on the jedrzej.kurylo answer and fixed mistakes I assume - whereMatchNumber - such function will not be existing:

public function getByMatchNumber($matchNumber)
    {
       return Match::with('video')
            ->where('match_number', $matchNumber)->first(); //->video->file_name;



    }

But it does not work. I get error:

Call to undefined method Illuminate\Database\Query\Builder::video()

Update

In Match.php model I added relation:

public function video()
    {
        return $this->belongsTo('Video', 'videos_id');
    }

Still getting the same error:

Call to undefined method Illuminate\Database\Query\Builder::videos()

Update: I had a mistake, was trying to add plural form when gettign error and forgot to undo:

return Match::with('videos')

needed:

return Match::with('video')

Update

Still not able to get result. I run this:

$video = $this->video->getByMatchNumber($this->matchNumber);

print_r($video->file_name);

I see good queries in query log:

 [1] => Array
        (
            [query] => select * from `matches` where `match_number` = ? limit 1
            [bindings] => Array
                (
                    [0] => 104439
                )

            [time] => 4.62
        )

    [2] => Array
        (
            [query] => select `file_name` from `videos` where `videos`.`id` in (?)
            [bindings] => Array
                (
                    [0] => 1089
                )

            [time] => 37.24
        )

I executed last query with the binding in HeidySQL and result is found. So why $video->file_name could be empty?

Update

Also I tried

die($video->video->file_name);

and get error

Trying to get property of non-object

You want to get a file name or video object by match number? Can be there multiple Matches with the same match_number? I can see you have both id and match_number in matches column so I'd assume match_number is not unique, otherwise it would be the key. You're also doing "limit(10)" which suggests there can be multiple videos for single match number. — jedrzej.kurylo
– jedrzej.kurylo, Commented Aug 4, 2015 at 13:44

jedrzej.kurylo · Accepted Answer · 2015-08-04 14:07:20Z

2

First of all, using Eloquent is not about getting rid of joins, but about easier manipulation of data using object notation. Depending on how you use it you'll fetch data using joins or without joins. And there is nothing wrong about using joins, as long as they're used properly.

To achieve what you need, the easiest way would be to do:

$match = Match::with('video')->whereMatchNumber($number)->first();
if ($match) {
  echo $match->video->file_name;
}

This will however run 2 queries in your database. If you want to do that using one query, you'll need to use joins:

$match = Match::join('videos', 'matches.videos_id', '=', 'videos.id')->whereMatchNumber($number)->first();
if($match) {
  echo $match->file_name;
}

answered Aug 4, 2015 at 14:07

jedrzej.kurylo

41.1k10 gold badges108 silver badges112 bronze badges

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

8 Comments

Dariux Over a year ago

for me this does not work. I updated the question, what I tried. And so far I do not find it easier to manipulate data :)

jedrzej.kurylo Over a year ago

You have too define relation to video in Match class. Do you have one? What is it called?

Dariux Over a year ago

I did not have one, because I read in some blog that no need relations in both ways, but now I added. Updated the question.

jedrzej.kurylo Over a year ago

Well, it's not needed if you don't use that, but here you obviously need one

jedrzej.kurylo Over a year ago

No, now you're getting a different error about videos() method, not video(). Didn't you use 'videos' instead of 'video' in with()?

|

Collectives™ on Stack Overflow

Laravel how to join tables and use where condition using Eloquent?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related