Nested query in FROM

replied 10 years ago

dunno, but if you are using too much db raw, consider directly writing your sql query using DB::statement

DB::statement( '

SELECT 
	modality , COUNT( modal ) AS total
FROM 
	( SELECT 
		modality AS modal, modality
	FROM
		series 
	GROUP BY 
		study_fk, modality
	) AS temp
GROUP BY
	modality


' );

Last updated 2 years ago.

0

lovaina

replied 10 years ago

Thanks arcollector! I've tried our code and... it doesn't work :(

I've tried this:

$modalities = Series::select(DB::statement( '
SELECT modality , COUNT( modal ) AS total
FROM 
    ( SELECT 
        modality AS modal, modality
    FROM
        series 
    GROUP BY 
        study_fk, modality
    ) AS temp
GROUP BY modality ' ));

And throw a database error (I'm working with two databases, the model works with B and the site with A), the error says "table A.series" doesn't exists", it's true.

Last updated 2 years ago.

0

lovaina

replied 10 years ago

2nd Round!!!

I've been searching and maybe this could work, but doesn't:

 $query = Series::select('count(modal) AS total', 'modality')
                ->from(function($sq)
                {
                   $sq->select('modality as modal', 'modality')
                        ->from('series')
                        ->groupBy('study_fk')
                        ->groupBy('modality');
                })
                ->groupBy('modality')
               ->get();

It says: Object of class Closure could not be converted to string

how I could solve it?

Last updated 2 years ago.

0

lovaina

replied 10 years ago

Solution

Finally I could do it XP This is the solution:

$query = Series::select(DB::raw('COUNT(*) as total, modality'))
                ->from(DB::raw('(SELECT modality AS modal, modality FROM series GROUP BY study_fk, modality) AS T'))
                ->groupBy('modality')
               ->get();

Last updated 2 years ago.

0

Moderators