How to include last updated row in RoR

So I have two models, Category and Topic and any category can have many topics.

The question is, how do I query for all categories and include each category's last updated topic?

If I do something like this

class Category < ActiveRecord::Base has_many :topics has_one :last_updated_topic, -> {order(updated_at: :desc)}, class_name: 'Topic' end

and query like that ->

Category.all.includes(:last_updated_topic)

then it works, but what scares me, is that I have a SQL query loading the topics like that:

SELECT "topics".* FROM "topics" WHERE "topics"."category_id" IN (1, 2, 3) ORDER BY "topics"."updated_at" DESC

so it loads all the topics, which there can be thousands, when I need only last updated ones for each category.

So I wonder if there is a simple way to solve this problem without writing custom SQL or if I am wrong about something?

Should I use this instead? ->

SELECT categories.*, t.title as last_topic_title FROM "categories" LEFT JOIN (SELECT topics.* FROM topics ORDER BY topics.updated_at DESC LIMIT 1) t ON categories.id = t.category_id

-------------Problems Reply------------

You can use inner query :

has_one :last_updated_topic, -> { where(updated_at: Topic.maximum(:updated_at)), class_name: 'Topic'

Category:sql Views:0 Time:2018-12-13

Related post

  • How to get ID of the last updated row in MySQL? 2009-09-07

    How do I get the ID of the last updated row in MySQL using PHP? --------------Solutions------------- I've found an answer to this problem :) SET @update_id := 0; UPDATE some_table SET row = 'value', id = (SELECT @update_id := id) WHERE some_other_row

  • Select the last updated row in oracle 2011-05-27

    How can I get the last updated row or record in an oracle database? I want to ensure that a trigger is fired to check whether the last updated row has all uppercase entries, and if not convert them to upper case. --------------Solutions-------------

  • How do I get updated row id in ZEND? 2010-11-29

    I use $table->update($data, $where); How do I get updated row id in ZEND? thanks. --------------Solutions------------- As with working with just the plain ol' SQL, just re-query for the info you want. Use $table->select('rowid')->where($wher

  • How to get last inserted row ID from wordpress database? 2009-10-16

    My wordpress plugin has a table with a AUTO_INCREMENT primary key field called ID. When a new row is inserted into the table, I'd like to get the ID value of the insertion. The feature is to using AJAX to post data to server to insert into DB. The ne

  • Select last updated row ID and last deleted row ID in trigger before they occur in SQL Server 2011-01-10

    I need to get the id of the updated row in a table to use it to update another table via trigger Also need to get the id of the deleted row in a table to use it to update another table via trigger How can I do this? Is there any built in functions in

  • How to get last inserted row in sql server 2011-01-11

    How can i get Last inserted row when in table there is no Uniqueidentifier or identity column. I am waiting of your good idea's. --------------Solutions------------- You need some way of being able to identify the ordering of the rows to determine th

  • How to select last N Rows without using an Index 2011-12-29

    I have a query that contains several conditions to extract data from a table of 5 million rows. A composite index has been built to partially cover some of these conditions to the extend that I am not able to cover the sorting with an index: SELECT c

  • How can I get updated row from MySQL in wordpress site? 2012-01-16

    I want to display the time and date of updated row from MySQL to wordpress site. How can I do that? --------------Solutions------------- Modified Date: <?php the_modified_date('F j, Y'); ?> at <?php the_modified_date('g:i a'); ?> Using th

  • Get the last updated row of sql table 2012-02-09

    I i have a SQL Server database table called tbl_faults. In that table fault_id is a primary(int) and auto incremented. In the same table i have another field called fault_reference (int), and not auto incremented. When i enter data to the table, faul

  • How to retrieve last update time for Excel file? 2013-02-24

    With workbook A.xls, I would like to retrieve last update time for Excel file, such as C:\B.xls without opening it. Does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions --------------Solutions-------------

  • How to select last inserted row by id? 2012-03-28

    that my problem: I have database table like that: id (AI) market_id 1 6 2 10 3 6 4 10 5 11 How to select last inserted market_id by id? --------------Solutions------------- You could do something like this: SELECT * FROM <your_table> ORDER BY i

  • How to read and update row in file with Java 2011-02-25

    currently i creating a java apps and no database required that why i using text file to make it the structure of file is like this unique6id username identitynumber point unique6id username identitynumber point may i know how could i read and find ma

  • How to get last new row of a growing CSV file? 2012-03-09

    I export data from a software to a CSV file, and the CSV file get new row every 5 minutes. Then, I want to get the newest row into another CSV or Excel file. No need to keep history, but need the newest row to be updated on real time. How can I do th

  • How does SQL Server Update rows with more than one value? 2014-08-12

    In an update statement for a temp table, how does SQL Server decide which value to use when there are multiple values returned, for example: UPDATE A SET A.dte_start_date = table1.dte_start_date FROM #temp_table A INNER JOIN table1 ON A.id = table1.i

  • How come the last 3 rows of cells in my table are not perfectly lined up with the 1st row in my HTML table? 2010-08-14

    Here is a link my example of the mis aligned table rows Click preview in the upper top left corner of the JS Bin menu bar to see this example in the works. You can see the top 2 borders of the table cells with Email and an input field in them are a b

  • how to edit and update row values in grid view? 2011-02-15

    I have a gridview like this : <asp:GridView ID="gvProducts" runat="server" AutoGenerateEditButton="True" AutoGenerateColumns="False" OnRowEditing="gvProducts_RowEditing" OnRowUpdating="gvProducts_RowUpdating" CellPadding="4" ForeColor="#333333" Gr

  • how to reverse last update 2014-04-02

    Clicking on folders is taking a long time to switch from one to another. Also, photos are not downloading. --------------Solutions------------- Hello Jerry, I would require additional information about this issue for further assistance. 1) Are are ex

  • how to get last updated value in sqlite3 2011-11-01

    Hello Friends, I have an application that will do the synchronization with the database to web-service. I have one table list in that I want to update, delete and insert the list. When the user click on the synchronization button I want to send the d

  • How do I include a custom row at the end of a DataGrid in Silverlight? 2009-02-04

    I have a DataGrid in my Silverlight application and it works nicely, adding a row or removing a row as I manipulate the ItemsSource collection. However, I want there to be an additional row, or control that always appears after the last data row. I c

  • Zend Framework: How to retrieve the id of the last inserted row? 2009-12-08

    I'm inserting a new row into my database with this code: $data = array( 'key' => 'value' ); $this->getDbTable()->insert($data); How can I get the row id of the this row that I just created? --------------Solutions------------- Did you try th

  • MySQL: How can I select only the last update for each user? 2010-04-06

    Say I have a 'user_log' table with the following field: id user_id status_text timestamp How do I write a query that selects only the last update for all the users in that table? Appreciate your help :) --------------Solutions------------- Assuming I

  • How do I delete every row except the last one for each day? 2010-05-07

    I have a table with stock quotes that looks something like this: id, date, stock_id, value Every day has several rows for each stock_id (it is automatically updated every five minutes), so the table is rather big at the moment. How do i delete every

  • How to know when a web page was last updated? 2010-07-06

    How can you detect when somebody else's web page was last updated (or was changed)? --------------Solutions------------- The last changed time comes with the assumption that the web server provides accurate information. Dynamically generated pages wi

  • How to retrieve only updated/new records since the last query in SQL? 2010-12-23

    I was asked to design a class for caching SQL query results. Calling the class' query method will query and cache the entire set of results at the first time; afterward, each subsequence query will retrieve only the updated portion, and will merge th

  • How to get the last updated date of each url in my website, using c# 2011-01-31

    I crawl my website and generate an xml sitemap. How do I get the date each page was last updated? The examples I find of c# code to create a sitemap just use the current date, or the last updated time in the response header, which is always the curre

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.241 (s). 11 q(s)