I make two samples by model Picture

 old = Picture.where(state: 'old').order("ctr DESC").limit(20) new = Picture.where(state: 'new').order("id DESC").limit(5)

How to make one of two ActiveRecord :: Relation? Moreover, the order of elements old should remain the same, and new should be located after the 5th element of old . The result should be like this (o - old, + - new)

 ooooo + + + + + ooooooooooooooo

A bit confusing, but very necessary)

PS Is it possible to perform such a task with one query in general, instead of the first two?

    3 answers 3

    In this case, you probably should use UNION. AR is not friendly with him. And arel, as it turned out, works very special for different DBMS and does not shield parts of the query with brackets (at least from the box).

    Apparently, it makes sense to do it yourself. But if you write completely yourself, the request will be intolerable to other DBMS. You can assemble portions of the query with AR tools and manually isolate them with brackets. Here is an example that I have worked on MySQL and PostgeSQL:

     scope1 = Location.where(location_type: 'country').order(id: :desc) scope2 = Location.where(location_type: 'city').order(label: :asc) sql = "( #{scope1.limit(1).to_sql}) UNION ALL (#{scope2.limit(2).to_sql}) UNION ALL (#{scope1.limit(2).offset(1).to_sql})" union = Location.find_by_sql(sql)
    • cool, it works too), but the result will be an array, and the update_all method will not work accordingly. And he is desirable) - Alex I
    • one
      Yes, with AR bobble out. But at least, all in one request. And why update_all need update_all , if not a secret? Perhaps it should be realized somehow differently? - anoam
    • I need to keep statistics on shows, each image on the main. those. A person came in, each picture gets +1 to views. - Alex I
    • So when updating, we don't need order. Picture.where(id: union.map(&:id)).update_all(<...>) Of course, I agree that my option as a whole is not as effective in terms of resource use, but I’ll choose another option in one request I do not know. - anoam
    • when updating the order is not important, I agree. but it is important when displaying elements on the page - Alex I

    For example:

     old_pictures[5, 0] = new_pictures # 5-й элемент меняем на new_pictures

    For your example:

     old = Picture.where(state: 'old').order("ctr DESC").limit(20) new = Picture.where(state: 'new').order("id DESC").limit(5) old[5, 0] = new

    After that, the old will be what you need.

    • I do not understand how this can help?) - Alex I
    • one
      @AlexanderIvanov wrote more in detail - MAXOPKA
    • I honestly don’t understand how, but it works. Except for one little thing. If you then do old.update_all (''), then all the elements will change, except for those new ones that we stuffed into old - Alex I
    • @ AlexanderIvanov I don’t know about the update. - MAXOPKA
    • one
      This functionality came to AR Enumerable. In this case, its use is not good. Immediately lost a lot of chips such as lazy requests, updates, mass updates and other things. - anoam 2:46 pm
     pics = Picture.where(state: 'old').order("ctr DESC").limit(5) pics << Picture.where(state: 'new').order("id DESC").limit(5) pics << Picture.where(state: 'old').order("ctr DESC").limit(15).offset(5)

    At the exit you get ActiveRecord :: Relation

    • it doesn't work like that when you try to iterate through the pics - undefined method `id 'for # <Picture :: ActiveRecord_Relation: 0x007fb27dddb5d0> - Alex I
    • pics.last - # <ActiveRecord :: Relation - Alex I
    • one
      N-yes, the problem takes place ... Good question, looking for a solution. - Pavel Volzhin
    • << this also seems to be from Enumerable, with all the consequences: users = User.where(id: 1) users << User.where(id: 2) users.count #=> 1 users.size #=> 2 users.to_sql #=> "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 1" users.reload users.size #=> 1 And yes, there probably means + rather than << << - adds the element to the end "as is", i.e. we get relation inside relation'a. - anoam
    • There should have been + = I did not rule, because the result is still not the same. - Pavel Volzhin