Filter query based on column value
I have a question, hopefully someone can help. I need to create a query in eloquent to get the orders based on the following information:
- they are located in the orders table
- the table structure is like this id, client_payment_from, delivery_date, customs_date, client_papers_date, transporter_papers_date, client_payment_in. In the client_payment_from column you will find the name of the column where you need to get the date value from and in the client_payment_in column the number of days that are allowed to pass.
I need to do a query where you can find the orders where that date is passed by the max amount of days (in the client_payment_in column)
12 Replies
This is not exactly Filament related... but from memory, I think you can do this kind of dynamic query with the sql
PREPARE statement.Thanks for your response. I've been looking for a solution for a 2 days now, I can't seem to find a way to do this query. I am not really familliar with the PREPARE statement, do you have any idea on now to achieve something like this?
I don’t think this is prepared statement. You need to use DB::raw in your eloquent query
And case when for your query
The column name is in a column, that's where the need for a prepared statement comes from. But I see what you mean, this could be 2 Eloquent queries instead.
Make sure you have functional index or it will be bad
Or use a virtual/generated column, with index
Prepared statement is more for binding, not writing custom sql in query builder
You can use the
PREPARE statement to build a dynamic query, in SQL.
@ciorici this is what I'm thinking about: https://stackoverflow.com/a/13283562
You can decide if it's a good solution for you, I don't know 😄Can filament table still works with this?
I think a table can work with a raw query, but I've never pushed it this far...
looks quite messy 😄
😅 same, but I think raw query is not returning eloquent and filament works with eloquent builder, not query builder
Personally, I would rethink the idea of having a column name inside a column... it would simplify things for you
Order::whereRaw(“(case when payment from = x then date column when from = y then … end + payment in) < curdate()”);
Maybe something like this. on mobile so not formatted
If it works consider have it as virtual column, else without index it’s gonna blow one day