FuelPHP Forums

I'm trying to run a query with three sub queries. I can get the result fine but the sub query rows are aliased by the entire sql query. I would like to select them AS something

 $vote_count = DB::select(DB::expr('count(id)'))
                ->from('promote_photos')
                ->where('photo_id', DB::expr('cid'))
                ->limit(1);

        $comment_count = DB::select(DB::expr('count(id)'))
                ->from('comment_photos')
                ->where('photo_id', DB::expr('cid'))
                ->limit(1);

        $voted = DB::select('id')
                ->from('promote_photos')
                ->where('photo_id', DB::expr('cid'))
                ->and_where('user_id', '=', $this->user_id)
                ->limit(1);


        $result = DB::select('*',DB::expr('photos.id as cid'),$voted,$comment_count,$vote_count)
                ->from('photos')
                ->join('places','left')
                ->on('places.id', '=', 'photos.place_id')
                ->join('profiles','left')
                ->on('profiles.user_id','=','photos.user_id')
                ->execute();

Which produces a query that looks like this

  SELECT *, photos.id as cid, (SELECT `id` FROM `promote_photos` WHERE `photo_id` = cid AND `user_id` = 1 LIMIT 1),
 (SELECT count(id) FROM `comment_photos` WHERE `photo_id` = cid LIMIT 1), 
(SELECT count(id) FROM `promote_photos` WHERE `photo_id` = cid LIMIT 1) 
FROM `photos` LEFT JOIN `places` ON (`places`.`id` = `photos`.`place_id`) LEFT JOIN `profiles` ON (`profiles`.`user_id` = `photos`.`user_id`)
         

This works but dealing with the data set is a mess.. for the sub queries outside the brackets there should be something like 'as voted' for example

  SELECT *, photos.id as cid, (SELECT `id` FROM `promote_photos` WHERE `photo_id` = cid AND `user_id` = 1 LIMIT 1) as voted, 
(SELECT count(id) FROM `comment_photos` WHERE `photo_id` = cid LIMIT 1) as comment_count,
 (SELECT count(id) FROM `promote_photos` WHERE `photo_id` = cid LIMIT 1)  as vote_count 
FROM `photos` LEFT JOIN `places` ON (`places`.`id` = `photos`.`place_id`)
 LEFT JOIN `profiles` ON (`profiles`.`user_id` = `photos`.`user_id`)

Or am I going about subqueries wrong?