how to join tables and left joins in a better way PostgreSQL

I have formulated this query

select *, (cameras_owned + camera_shares) total_cameras 
from ( select *, 
              (select count(cr.id) 
               from cloud_recordings cr 
               left join cameras c on c.owner_id=u.id 
               where c.id=cr.camera_id 
               and cr.status <>'off' 
               and cr.storage_duration <> 1),
              (select count(*) 
               from cameras cc 
               left join users uuu on cc.owner_id=uuu.id 
               where uuu.id=u.id) cameras_owned,
              (select count(*) 
               from camera_shares cs 
               left join users uuuu on cs.user_id=uuuu.id 
               where uuuu.id = u.id) camera_shares,
              (select count(*) 
               from snapmails sm 
               left join users suser on sm.user_id=suser.id 
               where suser.id = u.id) snapmail_count,
              (select name 
               from countries ct 
               left join users uuuuu on ct.id=uuuuu.country_id 
               where uuuuu.id=u.id) country,
              (select name 
               from companies cp 
               left join users uuuuuu on cp.id=uuuuuu.company_id 
               where uuuuuu.id=u.id) company_name,
              (select count(cs1.id) 
               from camera_shares cs1 
               where cs1.user_id=u.id 
                 and cs1.camera_id = 279) share_id
       from users u 
       where 1=1 
       order by created_at desc ) t

and its working all fine, my question is is there any better way of doing it?

Right now, there are 2 sorts and 2 where clauses

Such as

these all sorts values

["payment_method", "username", "name", "email", "api_id", "api_key", "cameras_owned", "camera_shares", "snapmail_count", "country", "company_name", "created_at", "last_login_at", "referral_url"]

work right after from users u {sorting and order}

and total_cameras work only after t part.

Same as I have multiple conditions, all conditions which are u. all go in from users u but then all other conditions which include

1. total_cameras
2. cameras_owned
3. camera_shares
4. share_id
5. company_name

these all above goes after t part,

I am looking for a solution where is it possible to write this query in such way that I can put sorting and all condition in one place instead of 2 2 different places?

I have formatted your query in the question. Now you can freely see that ALL your LEFT JOINs are in reality INNER JOINs due to null-rejecting conditions in the WHERE clause by a field from joined table.

Right now, there are 2 sorts and 2 where clauses

2 where clauses

COUNT(*) .. WHERE (condition) is equal to COUNT(CASE WHEN (condition) THEN 1 END).

2 sorts

I see one sorting only. And, by the way, it is posessed in subquery and, so, it will be ignored – if output seems to be sorted, it is random. – Akina

You don’t really need the LEFT JOINs to the users table in your sub-queries.

For example, this:

(select count(*) 
 from cameras cc 
 left join users uuu on cc.owner_id=uuu.id 
 where uuu.id=u.id) cameras_owned

can be simplified to

(select count(*) 
 from cameras cc 
 where cc.owner_id = u.id) cameras_owned

Same for the other count(*) subqueries. For readability I would also get the country and company name through a regular join, rather than a sub-query.

So the inner query would be something like this:

select *, 
        (select count(cr.id) 
         from cloud_recordings cr 
           join cameras c on c.owner_id = u.id 
         where c.id=cr.camera_id 
         and cr.status <> 'off' 
         and cr.storage_duration <> 1),
        (select count(*) 
         from cameras cc 
         where cc.owner_id = u.id) cameras_owned,
        (select count(*) 
         from camera_shares cs 
         where cs.user_id = u.id) camera_shares,
        (select count(*) 
         from snapmails sm 
         where sm.user_id = u.id) snapmail_count,
        ct.name as country,
        cp.name as company_name,
        (select count(cs1.id) 
         from camera_shares cs1 
         where cs1.user_id=u.id 
           and cs1.camera_id = 279) share_id
from users u 
  left join companies cp on cp.id = u.company_id 
  left join countries ct on ct.id = u.country_id
where 1=1 
order by created_at desc

Note that the order by in the inner query is not guaranteed to be preserved by the outer query (although very likely it will be). But nevertheless you should always move an order by to outermost query (unless you need it e.g. for a limit).