Is it possible to join another table based on 2 columns using with()?

Not sure what your current code looks like, so try something like this.

Model::with(['table_two' => function ($query) 
{
    $query->where('date_from', '...', '...')->where('date_to', '...', '...');
}]);

@ZION - I'm getting this error

Illuminate \ Database \ Eloquent \ RelationNotFoundException
Call to undefined relationship [table_two] on model 

This is what my code looks like

$data = TableOne::whereIn('user_id', $user_id)
                    ->where('date_from', $date_from)
                    ->where('date_to', $date_to)
                    ->with(['table_two' => function ($query) {
                        $query->where('date_from', $date_from, $date_to);
                    }])
                    ->get();

Modify it to how your relations are setup. You didn't post any code so I cannot know what it is supposed to be called. I took table_two from your OP.

How are your relations setup, if you have it setup ofcourse. If not, be more specific.

Update: You posted some code right before my reply. $query->where('date_from', $date_from, $date_to); should be $query->whereBetween('date_from', [$date_from, $date_to]); Other than that, is the relation called 'table_two'?

public function table_two()
{
            return $this->hasMany('App\Models\TableTwo');
}

but it says

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'table_two.table_id

These two table are not related by a foreign/local keys but by date_from and date_to columns

Ah I see. Well you can't use with when you have no relation setup.

What are your tables called? table_two seems like a bit of a weird table name. Anyway, use a regular join ( https://laravel.com/docs/5.7/queries#joins ).

How could I use a join() without overriding column names to end up with a structure like this?

[  
   {  
      "id":1,
      "user_id":75,
      "date_from":"2019-01-01",
      "date_to":"2019-01-31",
      "likes":"5987",
      "table_two": [
        {
            "id":1,
            "user_id":75,
            "date_from":"2019-01-01",
            "date_to":"2019-01-31",
            "reward":"candy",
        },
        {
            "id":2,
            "user_id":75,
            "date_from":"2019-01-01",
            "date_to":"2019-01-31",
            "reward":"juice",
        },
        {
            "id":3,
            "user_id":75,
            "date_from":"2019-01-01",
            "date_to":"2019-01-31",
            "reward":"candy",
        },
      ]
   },
]

Joins also require a foreign key. I thought I could get table_two later and push it into the original collection but it did not work like with(), it just added it at the end.

When you don't use convention naming, you have to specifiy how you link your data.

public function table_two()
{
            return $this->hasMany('App\Models\TableTwo','date_from', 'date_from');
}

Check : https://laravel.com/docs/5.7/eloquent-relationships#one-to-many

But if it has to contraint the two columns, I don't know if you can chain it :

public function table_two()
{
            return $this->hasMany('App\Models\TableTwo','date_from', 'date_from')->hasMany('App\Models\TableTwo','date_to', 'date_to');
}

But you can maybe simplify your architecture

Maybe there is a way to use with() without a relationship? Like passing a variable

$table_two = TableTwo::all();

$table_one = TableOne::with('table_two', $table_two);

I tried this but it was asking for a relationship instead of a collection :(

You can't, but you can add something to your collection.

$data = TableOne::whereIn('user_id', $user_id)
                    ->where('date_from', $date_from)
                    ->where('date_to', $date_to)
                    ->with(['table_two' => function ($query) {
                        $query->where('date_from', $date_from, $date_to);
                    }])
                    ->get();

$TableTwo = TableTwo::where('date_from', $date_from)
                    ->where('date_to', $date_to)
                    ->get();

$data->put('table_two', $TableTwo);
dump($data);

But if you have more than one user, it will not work... But you can maybe do something with the collections you get.

Why do you want/need that structure so bad? I don't see what the 2 tables have to do with each other. If you really need that structure one would assume there was some kind of relationship between the 2.

@VILFAGO - Yea, that's the thing, it is an array of users, so for each user there should be a key for 'table_two' with an array of that table's data. I may end up just using a bunch of collections (one for each table) instead of trying to join them all together in one collection... meh...

I think it's better to rethink you database structure...

Maybe using period in another table, and have a ManyToMany relations with this "period".

We can maybe help you if you give us more information on what you want to achieve.

@ZION - Simply so that I could avoid making a lot of collections and have just one collection to work with later...

So instead of doing this...

$data->table_one = TableOne::whereIn('user_id', $user_id)
                    ->whereBetween('date_from', [$date_from, $date_to])
                    ->whereBetween('date_to', [$date_from, $date_to])
                    ->get();

$data->table_two = TableTwo::whereIn('user_id', $user_id)
                           ->whereBetween('date_from', [$date_from, $date_to])
                           ->whereBetween('date_to', [$date_from, $date_to])
                           ->get();

$data->table_three = TableThree::whereIn('user_id', $user_id)
                                ->whereBetween('date_from', [$date_from, $date_to])
                                ->whereBetween('date_to', [$date_from, $date_to])
                                ->get();

$data->table_four = TableFour::whereIn('user_id', $user_id)
                              ->whereBetween('date_from', [$date_from, $date_to])
                              ->whereBetween('date_to', [$date_from, $date_to])
                              ->get();

$data->table_five = TableFive::whereIn('user_id', $user_id)
                            ->whereBetween('date_from', [$date_from, $date_to])
                            ->whereBetween('date_to', [$date_from, $date_to])
                            ->get();

I could do simply this...

$data = TableOne::whereIn('user_id', $user_id)
                ->whereBetween('date_from', [$date_from, $date_to])
                ->whereBetween('date_to', [$date_from, $date_to])
                ->with('table_two')
                ->with('table_three')
                ->with('table_four')
                ->with('table_five')
                ->get();

And I would end up with this

[  
   {  
      "id":1,
      "user_id":75,
      "date_from":"2019-01-01",
      "date_to":"2019-01-31",
      "table_two": [
        {
            "id":1,
            "user_id":75,
            "date_from":"2019-01-01",
            "date_to":"2019-01-31",
            "reward":"candy",
        },
      ]
      "table_three": [
        {
            "id":1,
            "user_id":75,
            "date_from":"2019-01-01",
            "date_to":"2019-01-31",
            "reward":"candy",
        },
      ]
   },
   
   {  
      "id":2,
      "user_id":75,
      "date_from":"2019-01-01",
      "date_to":"2019-01-31",
      "table_two": [
        {
            "id":1,
            "user_id":75,
            "date_from":"2019-01-01",
            "date_to":"2019-01-31",
            "reward":"candy",
        },
      ]
      "table_three": [
        {
            "id":1,
            "user_id":75,
            "date_from":"2019-01-01",
            "date_to":"2019-01-31",
            "reward":"candy",
        },
      ]
   },
]

Why your data is in 5 (or more) tables instead of one ?

It seems to refer always to the same user, with same period, so it could share a unique ID. And you can do the relation on this unique ID instead of 3 columns (user, date_to and date_from) ?

@VILFAGO - Because in one table are comments for that user and time period, then activities, logs, achievements etc. they can't all be in one table. So they are split between multiple tables.

If those all belong to a user you can setup a relation from and to a user. What is the point of date_to and date_from? Can't you just work with created_at/updated_at?

Can you not just provide a real world example instead of made up "table_one" etc. That would make it easier for us to understand and provide help.

@ZION - oh, that's a good point. They are all related by user_id so I could do a where on the date to filter them. I will give it a try.

• User Table (id, name, email, pwd, ...)
• Pivot Table (stuff_id, user_id)
• Period Table (id, stuff_id, date_from, date_to, text, ...)
• Log Table (id, stuff_id, text, ...)
• Activity Table (id, stuff_id, text, ...)
• etc.

And then, you can have a ManyToMany relations using always the same pivot table, and retrieve all the data related to the user, by "stuff".