Why does my Laravel Eloquent query return records with incorrect dates after applying a join with additional filters,despite initial date constraints?

I'm working with Laravel Eloquent to fetch records from a views table, filtering them by specific video_ids and created_at dates. I also need to join with another table devices to apply additional filters based on device types, regions, and hostnames.

Here's the structure of my tables:

views table:

id (Primary Key)
video_id
device_id (Foreign Key)
created_at (date-time string)

devices table:

id (Primary Key)
types
country
hostname
created_at (date-time string)

And here's the structure of my query:

// this is an example of what $dates looks like
// $dates = ['2024-08-01', '2024-08-02', '2024-08-03'];

public function getViews($videoIds, $dates, $devices, $regions, $sources){
    // Filter by video_id and created_at first
    $query = View::whereIn('views.video_id', $videoIds)
        ->whereIn(DB::raw('DATE(views.created_at)'), $dates);

    // Apply the join and other filters only if devices, regions, or sources are provided
    if (!empty($devices) || !empty($regions) || !empty($sources)) {
        $query->join('devices', 'devices.id', '=', 'views.device_id');

        $query->when(!empty($devices), function ($query) use ($devices) {
            return $query->whereIn('devices.types', array_map(fn($device) => $device['value'], $devices));
        });

        $query->when(!empty($regions), function ($query) use ($regions) {
            return $query->whereIn('devices.country', array_map(fn($region) => $region['value'], $regions));
        });

        $query->when(!empty($sources), function ($query) use ($sources) {
            return $query->whereIn('new_devices.hostname', array_map(fn($source) => $source['value'], $sources));
        });
    }

    return $query->get();
}

The problem I'm facing is that after applying the join, the result set includes views with created_at dates that are not in the $dates array I provided. I need the query to only return records where the created_at date is within the specified range, even after the join is applied.

My question is:

Why does the join cause the query to return records with created_at dates outside the specified range?
How can I modify my query so that it still respects the date filter after the join is applied?

I'm looking for a solution that ensures the result set only includes views with created_at dates within the provided $dates array, even when additional filters are applied through the join.

Any help would be greatly appreciated!

Solution

When performing join queries, always SELECT the columns you need. Suppose you have the following data ( skipping the unnecessary columns )

views

id	device_id	created_at
1	1	2024-01-01T00:00:00

devices

id	created_at
1	2024-08-02T00:00:00

If you perform a query like

SELECT * from views join devices on devices.id = views.device_id;

The database server would return a row like

id	device_id	created_at	id	created_at
1	1	2024-01-01T00:00:00	1	2024-08-02T00:00:00

Notice that there are two created_at here, views.created_at and devices.created_at

When this is read by PHP, it converts it to an object/array and these cannot have duplicate keys, it uses only the last duplicate key. So it would use the second array among the following

['id' => 1, 'device_id' => 1, 'created_at' => '2024-01-01T00:00:00'] // This is what you expected
['id' => 1, 'device_id' => 1, 'created_at' => '2024-08-02T00:00:00'] // This is what you got

This is likely the scenario you've encountered, the created_at you're seeing in your results is very likely from the other table.

To solve this, you can specify which table you want to select columns from using a select

View::select('views.*', 'devices.created_at as device_created_at', /* other columns you need from devices */)