Need Help on Join Table, Limiting Results to Only the Resource Id

Need help on join table, limiting results to only the resource ID

There are 2 ways I see that you can do this. The simpler approach (albeit less efficient in the sorting), would be to aggregate the sums by buyer_id and then inject that into the list of buyers:

<% buyer_sums = @seller.trans.group(:buyer_id).sum(:sum) %>
<% Buyer.where(id: buyer_sums.keys).sort_by {|b| -buyer_sums[b.id]}.each do |buyer| %>
  <%= buyer.name %> <%= buyer_sums[buyer.id] %>
<% end %>

A second approach would be to fetch the sums together with the buyer:

<% Buyer.joins(:trans).merge(@seller.trans).
         select("buyers.*, SUM(`sum`) sums").order('SUM(`sum`)').each do |buyer| %>
  <%= buyer.name %> <%= buyer.sums %>
<% end %>

The advantage of the second approach is that you aren't sorting after retrieving the entire collection, which matters for scalability especially as related to pagination.

Note that the backtick (`) escape character used here is for MySQL, but should be replaced with the appropriate DB convention if otherwise (e.g. double quote for PostgreSQL). Alternatively, use the quote_column_name method to do this smarter :)

In postgres, how can I execute queries that only return results where two many-to-many relationships overlap?

Using not exists to also cover the special case when a resource has no role (= it is public). Then we left outer join everything and see if we get a role of the resource for which the user doesn't have one.

SELECT DISTINCT
   r.id,
   r.name
FROM
   resource r
WHERE
   NOT EXISTS(
      SELECT
         *
      FROM
         resource_role_join rrj
         LEFT OUTER JOIN
            user_role_join urj
         ON
            urj.role_id = rrj.role_id AND
            urj.user_id = :USER_ID
      WHERE
         r.id = rrj.resource_id AND
         urj.role_id IS NULL
   )
;

Limit on join query using between

I found a solution using table unpivoting:

SELECT moment, value
FROM (SELECT IF(resourceId = ? AND @previousValue = 0, NULL, actualValue)       AS value,
             measurements.moment,
             resourceId,
             @previousValue := IF(resourceId <> ?, actualValue, @previousValue) AS enabled
      FROM (SELECT *
            FROM (SELECT moment,
                         Measurements.actualValue,
                         Measurements.resourceId AS resourceId
                  FROM Measurements
                  WHERE Measurements.resourceId = ?
                    AND moment BETWEEN ? AND ?
                  UNION (SELECT start,
                                periods.actualValue AS actualValue,
                                resourceId
                         FROM (SELECT COALESCE(@previousValue <> M3.actualValue,                                            1)                                                              AS "changed",
                                      (COALESCE(@previousMoment, ?))                                           AS "start",
                                      @previousMoment := M3.moment                                             AS "stop",
                                      COALESCE(@previousValue, IF(M3.actualValue = 1, 0, 1)) AS "actualValue",
                                      M3.resourceId                                                            AS resourceId,
                                      @previousValue := M3.actualValue
                               FROM Measurements `M3`
                                      INNER JOIN (SELECT @previousValue := NULL,
                                                         @previousMoment := NULL) `d`
                               WHERE (M3.moment BETWEEN ? AND ?)
                               ORDER BY M3.resourceId ASC, M3.moment ASC) AS periods
                         WHERE periods.changed)) AS measurements
            ORDER BY moment ASC) AS measurements
             INNER JOIN (SELECT @previousValue := NULL) `k`) AS mixed
WHERE value IS NOT NULL
  AND resourceId = ?;

This runs essentially runs table once per select, running ~40k x ~4k rows in 100ms.

SQL join issue with Laravel but id display are same

If you don't need any fields from a table, what you can do is, mention what are the fields required in the SQL query.

So, instead of SELECT * , you have to mention fields like this,

SELECT cat_name, 
       p_id, 
       created_at, 
       updated_at 
FROM   cats c 
       JOIN products p 
         ON p.cat_id = c.id 
WHERE  c.cat_name = 'FFVII' 

In your controller, You can get the result with the code below.

$result = DB::table('cats')
            ->join('products', 'products.cat_id', '=', 'cats_id')
            ->select('cats.cat_name', 'cats.p_id', 'cats.created_at', 'cats.updated_at', 'products.*')
            ->where('cats.cat_name', '=', 'FFVII')
            ->get();

MySQL left join limit to one row

For the expected result mentioned in your edit I would change the left joins to inner joins and select only country name with a group by clause. Note the foreign key names in the on clauses, I think you have to clarify/correct your table structures:

SELECT 
    table1.country

FROM
   table1 JOIN table2 ON table1.id = table2.table1_id
   JOIN table3 ON table2.id = table3.table2_id
   JOIN table4 ON table3.id = table4.table3_id

GROUP BY
    table1.country

MySQL join query only one row with reference table

You can try using correlated subquery

    SELECT product.*, description.body, price.currency FROM product
    LEFT JOIN 
    ( select * from product_description
      where product_description.id in (select max(id) from product_description b 
      where product_description.product_id=b.product_id)
    ) as x ON product.id = x.product_id
    LEFT JOIN product_price ON product.id = product_price.product_id
    LEFT JOIN description ON description.id = x.description_id
    LEFT JOIN price ON price.id = product_price.price_id

Joining two large tables (SQL)

You say that many share the same recourceID, but not the regionID. So joining on the combination of the two should not lead to that many records.

Maybe MySQL uses an index on recourceID only to build an intermediate result which it wants to scan for regionID matches. Hence the too many records.

So make sure you have an index on both fields in both tables:

create index idx_ri_both on RA_RegionInfo(regionID, recourceID);
create index idx_rt_both on RA_ResourceThresholds(regionID, recourceID);

This should lead to joining the matching records directly rather than joining more than needed in a first step.

I made regionID the first column in the indexes, because I gather this is the more selective one.

And by the way: the comma-separated join syntax was made redundant in 1992. It should not be used anymore. When interpreted literally this would be a cross join, i.e. two billion rows, that would be filtered by the where clause afterwards. (However, MySQL's optimizer should see through this and apply the join criteria directly.) Use explicit joins instead: FROM RA_RegionInfo ri JOIN RA_ResourceThresholds rt ON ....

And are you sure you really want to join the complete tables without any criteria? Usually such is not necessary, because one would be interested in particular data.

---

Related Topics