How to find instances of objects that share a common relationship to another object?
I would try the following:
# new scope in models/concert.rb
scope :suggestions_for_user, ->(user) {
joins(:genres).where(genres: { id: user.genres.select(:id) })
}
And use it like this:
user = User.find(...)
Concert.suggestions_for_user(user).distinct
Note the distinct
will be needed because of the nature of the database join the might return duplicate concerts.
Retrieve only 'id' values from a Record Association
http://guides.rubyonrails.org/association_basics.html#has_many-collection_singular
@user.article_ids
Rails 3: Retrieve all objects that have a certain value NOT equal to another value?
Using pure string condition queries, which are documented in the Rails Guides at http://guides.rubyonrails.org/active_record_querying.html#pure-string-conditions.
Review.where("stage != ?", "approve")
This can be vulnerable to SQL queries if you go using user values in the string condition, so use the array syntax as above, which replaces the question marks with each passed argument after sanitization.
Select rows with same id but different value in another column
This ought to do it:
SELECT *
FROM YourTable
WHERE ARIDNR IN (
SELECT ARIDNR
FROM YourTable
GROUP BY ARIDNR
HAVING COUNT(*) > 1
)
The idea is to use the inner query to identify the records which have a ARIDNR
value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.
Get records with max value for each group of grouped SQL results
There's a super-simple way to do this in mysql:
select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.
You avoid complicated subqueries that try to find the max()
etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)
Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.
Version 5.7 update:
Since version 5.7, the sql-mode
setting includes ONLY_FULL_GROUP_BY
by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).
How to select all records from one table that do not exist in another table?
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL
Q: What is happening here?
A: Conceptually, we select all rows from table1
and for each row we attempt to find a row in table2
with the same value for the name
column. If there is no such row, we just leave the table2
portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name
column (the one we are sure that exists, from table1
).
While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL
How to select rows with no matching entry in another table?
Here's a simple query:
SELECT t1.ID
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
The key points are:
LEFT JOIN
is used; this will return ALL rows fromTable1
, regardless of whether or not there is a matching row inTable2
.The
WHERE t2.ID IS NULL
clause; this will restrict the results returned to only those rows where the ID returned fromTable2
is null - in other words there is NO record inTable2
for that particular ID fromTable1
.Table2.ID
will be returned as NULL for all records fromTable1
where the ID is not matched inTable2
.
Related Topics
Resources for Database Sharding and Partitioning
How to Select a Row Based on Its Row Number
Postgresql Query to Excel Sheet
Freetds and Unixodbc Character Converting
Sql Query to Sum Fields from Different Tables
Query SQL Server Database from Native iOS Application
Query Temp Table in Stored Proc Whilst Debugging in SQL 2008 Management Studio
Selecting Multiple Rows by Id, Is There a Faster Way Than Where In
Pl/Sql- Get Column Names from a Query
Sql Server 2005 Unique Constraint on Two Columns
Efficient Time Series Querying in Postgres
Checking If a Given Date Fits Between a Range of Dates
How to Substitute a Left Join in Sql
Select The First Row in a Join of Two Tables in One Statement