Converting a SQL query into query builder. Select from several tables, Joins, where, when, order by

Hello,

I have a SQL query i need help translating into query builder. The query is for a search function, so its two parts. Main part when search input field is empty. Return all result. Second part is all the where clauses when something is typed into search input field. Im using livewire. only need help with the query builder.

So Raw SQL is as follows:

SELECT t1.date, t1.hours, t3.name AS resource, t2.customer, t1.type, t1.invoice, t1.description FROM timesheet AS t1 INNER JOIN customers AS t2 ON t1.customer = t2.id INNER JOIN users AS t3 ON t1.resource = t3.id

This block inside a when()

WHERE t1.date LIKE "%$search%" OR t1.hours LIKE "%$search%" OR t3.name LIKE "%$search%" OR t2.customer LIKE "%$search%" OR t1.type LIKE "%$search%" OR t1.invoice LIKE "%$search%" OR t1.description LIKE "%$search%"

ORDER BY t1.date DESC

Hoping someone here can help me. :) Thx in advance.

Tray2

5 years ago

Level 73

You can use the DB facade and just do

DB::select('<your query>');

In your case I would make a database view of the first part of the query and then use a simple query on it.

CREATE OR REPLACE view some_view AS
SELECT t1.date, t1.hours, t3.name AS resource, t2.customer, t1.type, t1.invoice, t1.description FROM timesheet AS t1 INNER JOIN customers AS t2 ON t1.customer = t2.id INNER JOIN users AS t3 ON t1.resource = t3.id;

Then create a model for it so you can

SomeView::where('field1', 'LIKE', '%' , $search , '%')
		->orWhere()
		->orWhere()
        ->get();

Please or to participate in this conversation.