How can I change "join with select statement results to" laravel eloquent?

Question

My query like this :

SELECT 
* 
FROM
(SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1 
INNER JOIN
(SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON t1.ks = t2.ks

I want to change it to laravel eloquent. I need to using paginate method from laravel eloquent

I try like this :

$query =  \DB::select(\DB::raw("
    SELECT *
    FROM (
        SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks
    ) t1 
    INNER JOIN (
        SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks
    ) t2 ON t1.ks = t2.ks 
"));

I'm confused to put paginate method. I need it. Because I want display pagination in view blade laravel

The script like this : {{ $results->appends(Request::query())->render() }}

How can I do it?

If you provide your database schema, sample data, and expected result, I suspect that query can be simplified, thus making it easier to implement using the query builder and paginator. — fubar, Aug 01 '18 at 02:29
@fubar Look at this : https://stackoverflow.com/questions/10538539/join-two-select-statement-results. My schema like that — moses toh, Aug 01 '18 at 02:32

fubar · Accepted Answer · 2018-08-01T03:41:50.943

Firstly, based on the linked question from which you sourced your database schema, the data you are attempting to query can be found using a far simplier query.

I have written the following query for use on a MySQL DBMS, as neither your, nor the linked question specify a DBMS type.

SELECT ks, COUNT(*) AS tasks, SUM(age < palt) AS late
FROM tasks
GROUP BY ks;

Here's an SQL Fiddle with sample data demonstrating the query works.

Secondly, the aforementioned query can be built using the Laravel query builder as follows.

$results = DB::table('tasks')
    ->selectRaw('ks, COUNT(*) AS tasks, SUM(age < palt) AS late')
    ->groupBy('ks')
    ->paginate(10);

Maybe you can help me again. Look at this : https://stackoverflow.com/questions/51838922/how-can-i-convert-many-statement-mysql-to-laravel-eloquent — moses toh, Aug 14 '18 at 10:12

How can I change "join with select statement results to" laravel eloquent?

1 Answers1