gistfile1.sql

select SQL_CALC_FOUND_ROWS 
    `al_mo`.`id`,`al_mo`.`name`,`al_mo`.`created_dts`,`al_mo`.`deleted`,`al_mo`.`description`,
    `al_mo`.`category_id`,
    (select `al_ca`.`name` from `category` `al_ca` where `al_ca`.`deleted` = "N" and `al_mo`.`category_id` = `al_ca`.`id` ) `category`,
    `al_mo`.`votes_support`,`al_mo`.`votes_abstain`,`al_mo`.`votes_oppose`, `al_mo`.`votes`,`al_mo`.`comments`,`al_mo`.`arguments`,
    `al_mo`.`flags`,`al_mo`.`user_id`,
    (select `al_us`.`name` from `user` `al_us` where `al_us`.`deleted` = "N" and `al_mo`.`user_id` = `al_us`.`id` ) `user_name`,
    `al_mo`.`last_comment_dts`, `al_mo`.`last_vote_dts`,`al_mo`.`last_flag_dts`,`al_mo`.`last_argument_dts`,`al_mo`.`url`,
    al_mo`.`is_hc`,`al_mo`.`motion_category_id`,`al_mo`.`video_id`,`al_mo`.`video_link`,`al_mo`.`tags`,
    `al_mo`.`image_id`,
    (select count(*) from `activity` 
        `al_ac` left join `user` as `_u` on `_u`.`id` = `al_ac`.`user_id` 
        left join `motion` as `_m` on `_m`.`id` = `al_ac`.`motion_id` 
        where `al_ac`.`deleted` = "N" and `al_ac`.`type` = 2001 and `al_ac`.`motion_id` = `al_mo`.`id` 
        and `al_ac`.`deleted` = "N" and `al_ac`.`created_dts` >= "1994-01-21 00:00:00" and 
        `al_ac`.`created_dts` <= "2014-01-21 23:59:59" ) `calc_votes3`,
    (select count(*) from `activity` `al_ac`
        left join `user` as `_u` on `_u`.`id` = `al_ac`.`user_id` left join `motion` as `_m` on `_m`.`id` = `al_ac`.`motion_id` 
        where `al_ac`.`deleted` = "N" and `al_ac`.`type` = 2002 and `al_ac`.`motion_id` = `al_mo`.`id` 
        and `al_ac`.`created_dts` >= "1994-01-21 00:00:00" and `al_ac`.`created_dts` <= "2014-01-21 23:59:59" ) `calc_flags`,
    `_u`.`deleted` `user_deleted`,`_u`.`surname` `user_surname`,`_f`.`thumb_file_id`,`_f_3`.`filename` `thumb_filename`,
    `_f_4`.`filename` `image_filename`,
    (select `al_ac`.`aux` from `activity` `al_ac` left join `user` as `_u` on `_u`.`id` = `al_ac`.`user_id`
        left join `motion` as `_m` on `_m`.`id` = `al_ac`.`motion_id` where `al_ac`.`deleted` = "N"
         and `al_ac`.`type` = 2001 and `al_ac`.`user_id` = "79" and `al_ac`.`motion_id` = `al_mo`.`id`
        order by `al_ac`.`id` desc limit 0, 1) `has_voted`,
    `al_mo`.`user_id` `_u`,`al_mo`.`image_id` `_f`,`_f`.`thumb_file_id` `_f_3`,`al_mo`.`image_id` `_f_4` 
from `motion` `al_mo` 
left join `user` as `_u` on `_u`.`id` = `al_mo`.`user_id` 
left join `filestore_image` as `_f` on `_f`.`original_file_id` = `al_mo`.`image_id` 
left join `filestore_file` as `_f_3` on `_f_3`.`id` = `_f`.`thumb_file_id` 
left join `filestore_file` as `_f_4` on `_f_4`.`id` = `al_mo`.`image_id` 
where `al_mo`.`deleted` = "N" and `al_mo`.`id` != 1
and (select count(*) from `activity` `al_ac` left join `user` as `_u` on `_u`.`id` = `al_ac`.`user_id`
    left join `motion` as `_m` on `_m`.`id` = `al_ac`.`motion_id` where `al_ac`.`deleted` = "N" 
    and `al_ac`.`type` = 2001 and `al_ac`.`motion_id` = `al_mo`.`id` and `al_ac`.`deleted` = "N" 
    and `al_ac`.`created_dts` >= "1994-01-21 00:00:00" and `al_ac`.`created_dts` <= "2014-01-21 23:59:59" ) != 0 
order by (select count(*) from `activity` `al_ac` left join `user` as `_u` on `_u`.`id` = `al_ac`.`user_id`
    left join `motion` as `_m` on `_m`.`id` = `al_ac`.`motion_id` where `al_ac`.`deleted` = "N"
    and `al_ac`.`type` = 2001 and `al_ac`.`motion_id` = `al_mo`.`id` and `al_ac`.`deleted` = "N"
    and `al_ac`.`created_dts` >= "1994-01-21 00:00:00" and `al_ac`.`created_dts` <= "2014-01-21 23:59:59" )
 desc limit 0, 10