-
Notifications
You must be signed in to change notification settings - Fork 230
-
Describe the bug
We are using left join as we need to allow sorting of each individual columns returned.
AFAIK this can't be supported using eager loading unless the sorting is done outside of SQL (Which is not very efficient).
The issue here is that results of the query are cached correctly in Redis but if we make changes to countries table, the cached item is not invalidated.
Can I confirm whether this is supported?
Here's a simplified schema for illustration:
Table: countries
+--------------+---------------+
| country_code | name |
+--------------+---------------+
| US | United States |
| JP | Japan |
+--------------+---------------+
Table: products
+----+--------+--------------+
| id | name | country_code |
+----+--------+--------------+
| 1 | apple | US |
| 2 | orange | JP |
+----+--------+--------------+
Model: Country
class Country extends CachedModel
{
public function products(): HasMany
{
return $this->hasMany(Product::class, 'country_code', 'country_code');
}
}
Model: Product
class Product extends CachedModel
{
public function country(): BelongsTo
{
return $this->belongsTo(Country::class, 'country_code', 'country_code');
}
}
Eloquent Query
Products::leftjoin('countries', 'country_code', '=', 'country_code')
->orderBy('countries.name');
Observations
Cached item is only tagged to "appmodelsproduct:entries" and not "appmodelscountry:entries".
Therefore when changes are made to countries table, the cached item is not invalidated.
Environment
- PHP: 8.2.3
- OS: RockyLinux 9.1
- Laravel: 10.7.1
- Model Caching: 0.13.4
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 7 comments
-
What if you use
protected $touches = ['Product'];
on Country Model can you confirm
I want to use the in one of my project
Beta Was this translation helpful? Give feedback.
All reactions
-
@geniqtech the key question is how you are making changes to the table. If you make the changes via Eloquent queries, the cache should be invalidated. If you make changes to the table data directly, the cache has no way of knowing you made updates.
Beta Was this translation helpful? Give feedback.
All reactions
-
@mikebronner We are making changes via Eloquent queries. Referring to the example in my use case above, if we make changes to countries table via Eloquent queries, the cached item is not invalidated.
Beta Was this translation helpful? Give feedback.
All reactions
-
@KimpShreyan AFAIK "$touches" only updates the "updated_at" column and is meant for a different purpose.
Beta Was this translation helpful? Give feedback.
All reactions
-
@geniqtech Could you provide the query you are using to make changes?
Beta Was this translation helpful? Give feedback.
All reactions
-
Here's the code snippet:
// Check before update
$data = Country::select('countries.*', 'products.name AS product_name')
->leftJoin('products', 'products.country_code', 'countries.country_code')->get();
Log::info($data);
// Update Product
$product = Product::find(1);
$product->name = 'banana';
$product->save();
// Check again after update
$data = Country::select('countries.*', 'products.name AS product_name')
->leftJoin('products', 'products.country_code', 'countries.country_code')->get();
Log::info($data);
Logs Output
local.INFO: [{"country_code":"US","name":"United States","product_name":"apple"},{"country_code":"JP","name":"Japan 1","product_name":"orange"}]
local.INFO: [{"country_code":"US","name":"United States","product_name":"apple"},{"country_code":"JP","name":"Japan 1","product_name":"orange"}]
Beta Was this translation helpful? Give feedback.
All reactions
-
Hello guys!
Any updates in this?
I'm also having some issues with join, seems to not be invalidating cache correctly, is it supported ?
This is how I'm populating the database:
PostComment::create([
'comment' => $message,
'post_id' => $taskId,
'created_by' => $user->id
]);
How i'm querying:
$postComments = Post::select(
'user.id',
'user.uuid',
'user.name',
'post_comments.created_at',
'post_comments.comment'
)->join(
'post_comments',
'post_comments.task_id',
'posts.id'
)->join(
'users',
'user.id',
'post_comments.created_by'
)->where('posts.uuid', $uuid)->orderBy('post_comments.created_at', 'desc');
This query doesn't show any updated results.
But, if I change to this:
$postComments = PostComments::select(
'user.id',
'user.uuid',
'user.name',
'post_comments.created_at',
'post_comments.comment'
)->join(
'posts',
'posts.id',
'post_comments.task_id'
)->join(
'user',
'user.id',
'post_comments.created_by'
)->where('posts.uuid', $uuid)->orderBy('post_comments.created_at', 'desc');
it works, but before I updating my laravel-model-caching the first query was working properly if I'm not wrong.
Beta Was this translation helpful? Give feedback.