Postgresql – re-write active record query to PostgreSQL

postgresqlrails

I want to rewrite the active record query to Actual PostgreSQL query

Camera.joins("left JOIN users on cameras.owner_id = users.id")
                    .joins("left JOIN vendor_models vm on cameras.model_id = vm.id")
                    .joins("left JOIN vendors v on vm.vendor_id = v.id")
                    .where(condition).order(sorting(col_for_order, order_for))

as you can see how these tables are related in the above joins.

also conditions could be

"lower(cameras.exid) like lower('%#{params[:fquery]}%') OR lower(cameras.name) like lower('%#{params[:fquery]}%') OR
      lower(vm.name) like lower('%#{params[:fquery]}%') OR lower(v.name) like lower('%#{params[:fquery]}%')
      OR lower(users.firstname || ' ' || users.lastname) like lower('%#{params[:fquery]}%') OR
      lower(cameras.config->>'external_host') like lower('%#{params[:fquery]}%')

Reason of writing this query, I have another table camera_shares which is related to cameras table with camera_id, I also want to get the count of camera_shares against a camera_id, How could that be possible to geth the similar results in PostgreSQL

Best Answer

I'm not sure about all your condition stuff but going on your textual description of the problem..

Reason of writing this query, I have another table camera_shares which is related to cameras table with camera_id, I also want to get the count of camera_shares against a camera_id, How could that be possible to geth the similar results in PostgreSQL

You'll want something like this...

SELECT camera_id, count(*)
FROM cameras AS c
JOIN camera_shares AS cs
  USING (camera_id)
GROUP BY camera_id;