Dec 23, 2022

Level 1

How to manage product eloquent query result based on category expire time?

How to manage product eloquent query results based on category finish time?

I'm working feature that requires the list of product displays on a google map with the category but based on some finishing time

tables:

product

id
category_id (array: ['1','2','3'])
location
lat
long
long

category

id
title
is_finish (yes/no)
finish_time (in minutes)

let's say

category "[1]. clothes" has finish_time = 80 min.

category "[2]. kitchen" has finish_time = 50 mins.

then product "A" with the category = "['1','2']"

so, all I have to create a product query for the listing results that the category (clothes and kitchen) product will disappear from the list after 80 mins. and 50 mins.(finish_time) respectively.

Query:

$product = $product->with(['Category', 'Photos','user'])
                    ->havingraw('distance < ' . $distance . '')
                    ->get();

But I'm getting no clue how to do this, how to manage this product list based on category finished time.

any help would be appreciated!

Thank you.

vincent15000

3 years ago

Level 63

Not clear enough for me, I have understood that you need to retrieve the products list for only products that have a specific category finish_time value. If that's it, this should help you.

$products = Product::with(['categorie' => function ($query() {
	$query->where(...); // here you check finish_time or is_finished
}])->get();

M@rty

3 years ago

Level 1

@vincent15000 thanks for the reply. I need to retrieve the all-products list with a specific category finish_time value that product "A" will last in the result only for 50mis. because having category no 2. "clothes" and also the last 80min. because having a category "Kitchen".

I want to disappear products from the list based on category finish time.

1 like

PovilasKorop

3 years ago

Level 11

@m@rty if you can still change the structure of your DB, I would advise to change it to many-to-many relationship instead of category_id with array of IDs - this is actually the reason why it's problematic for you now to query this.

So, you would have a category_product table with category_id and product_id

Then in the Product model, define the relationship:

public function categories() {
    return $this->belongsToMany(Category::class);
}

And then, finally, would be able to use something similar to @vincent15000 suggested:

Product::whereHas('categories', function($query) {
    $query->where('finish_time', '<', $whateverYouWant);
});

1 like

M@rty

3 years ago

Level 1

@PovilasKorop Right, actually it makes sense, why didn't I think of this let me change the structure first

1 like

M@rty

3 years ago

Level 1

@vincent15000 @povilaskorop I did change the database structure as per your suggestion. I got a list of products having categories by the whereHas() clause.

now seems I'll still have a problem as I have a field named "is_finish" = "yes/no" for the respective category.

if "is_finish = no", the finish_time field will be stored a null. if "yes", whatever time.

Cases:

) The product with a category having is_finish = no, that product always will keep in the result list (Here I do not have to check the "finish_time < whatevertime" )
) The Product list with a category having the is_finish = yes, that product will disappear from the list after whatever time of created_at. (Here I do need to check the "finish_time < whatevertime")
) category and their finish_time will be dynamic

Query:

Product::whereHas('categories', function($query) {
	$query->where('is_finish', 'yes'/'no'); // if yes then check finish_time, if no then keep product into the list
    $query->where('created_at', '>=', Carbon::now()->addMinutes($whatever_finish_time_of_X_category)); // $whatever_finish_time_of_X_category >> stores in DB table "categoty"
});

what should I do with these cases, If I want to manage with a single query, is it possible? plus what is the best way to manage the below scenario,

    $query->where('created_at', '>=', Carbon::now()->addMinutes($whatever_finish_time_of_X_category)); // $whatever_finish_time_of_X_category >> stores in DB table "category"

if Category = kitchen,  then finish_time will be $whatever_finish_time_of_X_category
if Category = clothes then finish_time will be $whatever_finish_time_of_X_category
if Category = XYZ, then the finish time will be $whatever_finish_time_of_X_category
and so on...

I'm getting deeper and more confused than seems I guess :(

Edit:

or Should I just go with the case like, managing the "finish_time" with the product table while storing? and fetch those product whose expire_time < now() or something? if yes, then what is a better way to store non-expire category product time in a table?

Many Thanks!

1 like

webrobert

3 years ago

Level 51

You’re confused because you haven’t thought out the naming. A category that expires is confusing. Never heard of that. Have you? Things like this contribute to the mental mess. Trying to understand meaning.

Promos expire. Coupons expire. Campaigns expire.

1 like

M@rty

3 years ago

Level 1

@webrobert yes exactly, I never heard of this

yes, Promos expire. Coupons expire. Campaigns expire. that is the valid

but this is (removing products from the list based on the category finish time) I have to do :(

Please or to participate in this conversation.