11/15/2023 0 Comments Elixir ecto subquery![]() ![]() ![]() Select: %Įither way, the query will give you one device with its info and most recent location all in one query. # You can structure this select any way you like Inner_lateral_join: dl in subquery(location_query), # Use the same alias as specified in the subquery Inner_join: di as assoc(pd, :device_info), Then join to it from ProvisionedDevice: query = # Order by inserted at to get the most recent entry Where: parent_as(:d_info).id = dl.device_info_id, You will want to create a subquery for the locations: location_query = Or, alternatively, Enum.I don't know if this can be done in a preload, but you can use lateral joins (section 7.2.1.5) to achieve what you want. Quote bind_quoted: doĭefmacro is necessary to allow pin operators out of context. |> join(:left,, c in Comment, do_join(approved, featured)) ![]() I would go with declaring a helper and pattern match arguments in it: def posts_with_comments(approved, featured, some_var) do I would expect a bare bones macro like this to work: defmacrop do_join doīut it doesn't. For some reason, it doesn't work with the keyword list either. I'm not exactly a macro expert yet but I don't see how the nil match can work at run time. I couldn't get macro based solutions to work. dynamic does not seem to work with the keyword list. The keyword list (which I assume implies =) and the more expressive format. Join seems to support two types of on parameters. ![]() The problem I had there was making the field name (e.g., c.approved) work from a variable. That's much better than my first attempt because it's a simple concatenation that just gets longer with more conditions rather than an explosion of conditions.Īs an exercise I have been unable to make this more generic by feeding it a list of fields and using something like reduce. _ -> dynamic(, ^dynamic and c.featured = ^featured) _ -> dynamic(, ^dynamic and c.approved = ^approved) Join(query, :left,, c in Comment, ^do_join(approved, featured))ĭynamic = dynamic(, c.post_id = p.id) def posts_with_comments(approved, featured) do (leaving out the some_var condition I had earlier). I think the answer is to use the dynamic function. I can't put those conditions in a where because if I do something like where t.approved = true I get only posts approved comments. My attempts to do that have failed because of the requirement to pin. I'm looking for a way to dynamically build that list for the on parameter of join(). It would get crazy if I had a third parameter. That works but there must be a better way. True -> join(query, :left,, c in Comment, ) I haven't found a better way than something like this: def posts_with_comments(approved, featured, some_var) do I'm trying to write a function something like this where if approved or featured is not nil, they will be included in the join, if they are nil, they will be ignored. I want to optionally filter comments on approved and featured. I want comments preloaded but preferably one SQL query. I want to get all Posts regardless of whether they have comments or not, hence the left join. Then the code would look like this: maxrows 500 Purchases > where ( p, p. You could use ectocursorbasedstream library to achieve it. 1st point is also not our use case as its later pattern-matched in another function clausule. The important part of this check is that we all those cases: 2 maps. Let's say Comment has two boolean fields, approved and featured. As a workaround I recommend streaming the rows without the need of encapsulating that with a transaction, e.g. In this we are checking if there are 2 different structs (which is not our use case). I'll try to describe it with the typical posts and comments example. I'm trying to build an Ecto query with a left join with optional extra conditions on the join. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |