Is there any way to simplify this query?

Question

MySQL

Hi all!
I have a query consisting of 4 UNION ALL and one of them contains the following lines:

' LEFT JOIN (SELECT * FROM (SELECT carid, orderid, link FROM '. $this->db->dbprefix .'cars_photo WHERE crop = 1 ORDER BY orderid DESC) t GROUP BY t.carid) cp ON (cp.carid = fwc.carfollow)' .

if I understand correctly what I did - the query first pulls out ALL the data, and then in all of them it selects one by the last ON( ... )
Is it possible to somehow simplify it so that it directly selects 1 element for 1 select ? I can't get it right
thanks in advance
---------------------

public function get_feed($username, $offset, $limit)
  {
    $sql = '(SELECT 1 as type, crs.id as id, carfollow, crb.title, crb.text, crb.date, crb.owner, crb.id as post_id, crs.name as car_name, crm.mark as car_mark, crd.model as car_model, cp.link ' .
    ' FROM '. $this->db->dbprefix .'follow_car fwc' .
    ' JOIN ' . $this->db->dbprefix . 'cars_blog crb ON (crb.carid = fwc.carfollow) ' .
    ' LEFT JOIN ' . $this->db->dbprefix . 'cars crs ON (crs.id = fwc.carfollow) ' .
    ' LEFT JOIN ' . $this->db->dbprefix . 'cars_mark crm ON (crm.label = crs.mark) ' .
    ' LEFT JOIN ' . $this->db->dbprefix . 'cars_model crd ON (crd.label = crs.model) ' .
    ' LEFT JOIN (SELECT * FROM (SELECT carid, orderid, link FROM '. $this->db->dbprefix .'cars_photo WHERE crop = 1 ORDER BY orderid DESC) t GROUP BY t.carid) cp ON (cp.carid = fwc.carfollow)' .
    ' WHERE fwc.username = ? AND crs.hide = 0 AND crb.status = 1) ' .
    ' UNION ALL ' .
    ' (SELECT 2 as type, usr.id as id, userfollow, usb.user_blog_title, usb.user_blog_text, usb.cdate, NULL, usb.post_id, fwu.userfollow, NULL, NULL, usr.avatar ' .
    ' FROM '. $this->db->dbprefix .'follow_user fwu ' .
    ' JOIN ' . $this->db->dbprefix . 'user_blog usb ON (usb.user_name_blog = fwu.userfollow) ' .
    ' LEFT JOIN ' . $this->db->dbprefix . 'users usr ON (usr.username = fwu.userfollow) ' .
    ' WHERE fwu.username = ? AND usb.status = 1) ' .
    ' UNION ALL ' .
    ' (SELECT 3 as type, str.item_id as id, userfollow, str.item_title, str.item_descr, str.date, NULL, str.item_id, fsu.userfollow, NULL, NULL, usr.avatar ' .
    ' FROM '. $this->db->dbprefix .'follow_store fsu ' .
    ' JOIN ' . $this->db->dbprefix . 'store str ON (str.seller = fsu.userfollow) ' .
    ' LEFT JOIN ' . $this->db->dbprefix . 'users usr ON (usr.username = fsu.userfollow) ' .
    ' WHERE fsu.username = ?) ' .
    ' UNION ALL ' .
    ' (SELECT 4 as type, grt.id, groupfollow, grt.title, grt.text, grt.date, grt.author, grt.groupid, gr.title, NULL, NULL, gra.link ' .
    ' FROM '. $this->db->dbprefix .'follow_group fgr ' .
    ' JOIN ' . $this->db->dbprefix . 'groups_topics grt ON (grt.groupid = fgr.groupfollow) ' .
    ' LEFT JOIN ' . $this->db->dbprefix . 'groups gr ON (gr.id = fgr.groupfollow) ' .
    ' LEFT JOIN ' . $this->db->dbprefix . 'groups_avatar gra ON (gra.groupid = fgr.groupfollow) ' .
    ' WHERE fgr.username = ?) ' .
    ' ORDER BY date DESC' . 
    ' LIMIT '.$offset.','.$limit;
    
    $query = $this->db->query($sql, array($username, $username, $username, $username));
    return $query->result_array();
  }

Answer 1

one-time request. it is not needed in the general query and can be executed before the general
You can simplify. Save the query in a separate table, and then pull out one value at a time.

Answer 2

Try

SELECT TOP 1 carid, link 
  FROM cars_photo 
 WHERE crop = 1
   AND cars_photo.carid = fwc.carfollow
 ORDER BY orderid DESC

and without GROUP BY.