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

Question

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!

Before performing join operation does the $query returns the records withing the given range provided by $dates ?Are you sure that $dates contains only the date excluding the time part in the array? — Subha
– Subha, Commented Aug 24, 2024 at 15:33
@Subha Yes, Before performing the join operation $query returns the records within the given range provided by $dates. And yes, $dates contains only the date excluding the time part in the array — Tammy06
– Tammy06, Commented Aug 24, 2024 at 16:42
Why am I seeing array_map() calls where array_column() belongs? — mickmackusa
– mickmackusa ♦, Commented Aug 30, 2024 at 15:03

Arun A S · Accepted Answer · 2024-08-25 08:17:08Z

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 */)

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related