Show All the Table Entries That Belong to a Specific Entry in Rails

Show all the table entries that belong to a specific entry in Rails

This is because you're using <%= in the line with the each statement, which means the result is outputted to the HTML, instead of <% which does not. The result of an each call is an enumerator object, which is the output you see after the title. The correct code would be:

<% @sub.posts.each do |p|%>
  <%= p.title %>
<% end %>

Rails : How to display all the associated records from table B based on every records from table A

What you have there is a one-to-many relation. You can implement that with Active Record associations https://guides.rubyonrails.org/association_basics.html.

For example your table A could be an Author model and your table B could be a Book model

class Author < ApplicationRecord
  has_many :books, dependent: :destroy
end

class Book < ApplicationRecord
  belongs_to :author
end

So your controller could search for your authors

class CatalogController < ApplicationController
  def list
    @authors = Author.all
  end
end

On your view iterate over authors and theirs books(A.datecol as Author.name, B.description as Book.title )

<ul>
  <% @authors.each do |author| %>
    <li><span><%= author.name %></span>
      <ul>
        <% author.books.each do |book| %>
          <li><%= book.title %></li>
        <% end %>
      </ul>
    </li>
  <% end %>
</ul>

Rails - Efficient way of finding entries with specific value in model

Sounds like you want to do a where query, i.e.

@records = Model.where(:some_column => some_value)

Rails has excellent documentation, I suggest you take a look at the ActiveRecord Query guide:

http://guides.rubyonrails.org/active_record_querying.html

ian.

Get all records that exist in both the tables

What you need is INNER JOIN where joining condition must be the ids of the table

Upload.joins("INNER JOIN medias ON media.id = uploads.id")

This will be translated to:

"SELECT `uploads`.* FROM `uploads` INNER JOIN media ON media.id = uploads.id"

Edit:

As MrYoshiji said in this comment, Upload.where(id: Media.all) also will do a single query.

So both options option will return all the upload records which ids exists in the media table

Select records all of whose records exist in another join table

In PostgreSQL it can be done with a single query. (Maybe in MySQL too, I'm just not sure.)

So, some basic assumptions first. 3 tables: clubs, users and books, every table has id as a primary key. 3 join tables, books_clubs, books_users, clubs_users, each table contains pairs of ids (for books_clubs it will be [book_id, club_id]), and those pairs are unique within that table. Quite reasonable conditions IMO.

Building a query:

1. First, let's get ids of books from given club:

   SELECT book_id
   FROM books_clubs
   WHERE club_id = 1
   ORDER BY book_id
   

2. Then get users from given club, and group them by user.id:

   SELECT CU.user_id
   FROM clubs_users CU
     JOIN users U ON U.id = CU.user_id
     JOIN books_users BU ON BU.user_id = CU.user_id
   WHERE CU.club_id = 1
   GROUP BY CU.user_id
   

3. Join these two queries by adding having to 2nd query:

   HAVING array_agg(BU.book_id ORDER BY BU.book_id) @> ARRAY(##1##)
   

   where ##1## is the 1st query.

   What's going on here: Function array_agg from the left part creates a sorted list (of array type) of book_ids. These are books of user. ARRAY(##1##) from the right part returns the sorted list of books of the club. And operator @> checks if 1st array contains all elements of the 2nd (ie if user has all books of the club).

4. Since 1st query needs to be performed only once, it can be moved to WITH clause.

Your complete query:

WITH club_book_ids AS (
  SELECT book_id
  FROM books_clubs
  WHERE club_id = :club_id
  ORDER BY book_id
)
SELECT CU.user_id
FROM clubs_users CU
  JOIN users U ON U.id = CU.user_id
  JOIN books_users BU ON BU.user_id = CU.user_id
WHERE CU.club_id = :club_id
GROUP BY CU.user_id
HAVING array_agg(BU.book_id ORDER BY BU.book_id) @> ARRAY(SELECT * FROM club_book_ids);

It can be verified in this sandbox: https://www.db-fiddle.com/f/cdPtRfT2uSGp4DSDywST92/5

Wrap it to find_by_sql and that's it.

Some notes:

• ordering by book_id is not necessary; @> operator works with unordered arrays too. I just have a suspicion that comparison of ordered array is faster.
• JOIN users U ON U.id = CU.user_id in 2nd query is only necessary for fetching user properties; in case of fetching user ids only it can be removed

Custom query to fetch all entries of a table and that only contains first of many duplicates based on a specific column

What you need is a distinct on proposed to RoR quite recently here but not yet merged, as pointed out by @engineersmnky. In a raw SQL from it will look like this:

select distinct on (ip_address) * 
from test 
where id<>1 
order by ip_address,created_at;

Which would translate to RoR's

self.where("id <> 1").distinct_on(:ip_address)

or, until the new feature gets accepted:

self.where("id <> 1").select("distinct on (ip_address) *")

Full db-side test:

drop table if exists test cascade;
create table test (
    id serial primary key,
    name text,
    vin integer,
    ip_address inet,
    created_at timestamp,
    updated_at timestamp);
insert into test 
(id,name,vin,ip_address,created_at,updated_at)
values
(0,'default', 0,'0.0.0.0/0'::inet,'2021-11-08 11:54:26.822623'::timestamp,'2021-11-08 11:54:26.822623'::timestamp),
(1,'admin',   1,'10.108.150.143'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(2,'V122',    122,'10.108.150.122'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(3,'V123',    123,'10.108.150.123'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(4,'V124',    124,'10.108.150.124'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(5,'V122',    122,'10.108.150.122'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp),
(6,'V125',    122,'10.108.150.125'::inet,'2021-11-08 11:54:26.82885'::timestamp,'2021-11-08 11:54:26.82885'::timestamp);

select distinct on (ip_address) * 
from test where id<>1 
order by ip_address,created_at;
-- id |  name   | vin |   ip_address   |         created_at         |         updated_at
------+---------+-----+----------------+----------------------------+----------------------------
--  0 | default |   0 | 0.0.0.0/0      | 2021-11-08 11:54:26.822623 | 2021-11-08 11:54:26.822623
--  2 | V122    | 122 | 10.108.150.122 | 2021-11-08 11:54:26.82885  | 2021-11-08 11:54:26.82885
--  3 | V123    | 123 | 10.108.150.123 | 2021-11-08 11:54:26.82885  | 2021-11-08 11:54:26.82885
--  4 | V124    | 124 | 10.108.150.124 | 2021-11-08 11:54:26.82885  | 2021-11-08 11:54:26.82885
--  6 | V125    | 122 | 10.108.150.125 | 2021-11-08 11:54:26.82885  | 2021-11-08 11:54:26.82885
--(5 rows)

---

Related Topics