Thanks @outlog! I’ve read the What’s new in Ecto book and recall some stuff on Schemaless queries, but will watch the video (and/or review the book) and see if anything in there helps me understand a different approach.
bglusman
bglusman
Possibly? I’m unclear how or what ordering would really matter for here, I think of order_by for sorting and though I sort at the end by credit_type name, that part is trivial because there’s generally only 3 credit types…
What I really want, ideally, is to get Ecto to return the CreditType structs with stocks preloaded, which it does now, but in the process it pulls all foods from the DB which is inefficient… it just seems like there’s no good way to preload stocks without also preloading all the joining schemas/models along the way.. I do also need the foods from those stocks, but not foods that are “dead ends” and do not lead to any stocks for the particular facility id provided.
0
brightball
Not going to disagree too much. Mainly just wanted to toss it out as an option.
In terms of the app logic separation though, just keep in mind that there are some types of app logic that have been trumpeted by frameworks with the goal of database portability that actually negatively impact your application. The database is the only thing that can actually guarantee database integrity since otherwise you’re prone to race conditions.
Functions are really simple and powerful in Postgres though and the type of structure outlined in that post is a couple of lines of code around a query…that roughly translates to the equivalent of a database view with arguments.
The only thing I’d provide as some parting wisdom is this; at the point that you’re performance tuning your data storage or retrieval, all options should be on the table. I’ve seen cases where people were so committed to the idea of avoiding leveraging their database that they were more willing to add 3rd party tools as a workaround (like Redis/Elasticsearch) than using the tooling already in the database.
I don’t think it’s necessary in your case right now, just always be aware that your database (especially PG) is a really powerful thing. Take advantage. Don’t keep a Tesla Roadster in the garage to get groceries. 
2
cmkarlsson
@bglusman I think @kip is right on this one. If you are not doing aggregates (like counting the number of stocked items for a particular type) group by is not needed.
It is hard to say what is wrong without seeing the error messages you get from your query.
A few things to help your troubleshooting:
- Does the raw SQL work in psql?
- If it works, try it with and without the group by clause and then you can confirm they do the same thing
- Does the ecto query work without the group by clause?
- Strip down the query until it works and then add in more elements to get to where you want to be. For example start with just joining one table, make sure the result make sense and then continue with the next one.
What I can see in the link provided it cannot work as your group by clause only contains column whereas your select contains many. Any non-aggregated field in the select clause must be part of the group by clause.
As a side-note. Other’s say this is complicated SQL but this is as straight-forward as it gets. A simple select with a few joins and a where clause.
1
OvermindDL1
Entirely agree. Complex SQL is what I have to deal with on this Oracle horror at work where the average SQL query is 2 printed-pages in length, and the longest are well over 10 printed pages… >.>
I do fight Ecto a lot with these sadly, and have to toss out a lot of Ecto when I have to start doing cross-schema joins that Ecto entirely does not support… 
0
cmkarlsson
Agreed. Ecto is getting there but like most “ORMs” they optimise for the trivial case which doesn’t exist in real life
Unfortunately I’ve spent way too much time trying to translate SQL queries into ecto (or other libraries). I still do it because they do add value, but for lots of queries I just use raw SQL.
1
OvermindDL1
Yeah, I’ve not had a project yet where I did not have to break out of Ecto some-how… ^.^;
I especially like it as a Static Typing and Translation layer on top of SQL. It catches stupid little typing bugs that I make, which consequently is also the main reason why I love statically typed languages.
2
bglusman
Great suggestion @cmkarlsson, simplify! I know I tried some version of simplifying, but probably not enough. The raw sql query does work, and I just confirmed the group by has no effect other than ordering… will report back when I have more to report, thanks to you and @kip and @OvermindDL1 for thoughts!
1
bglusman
Parting thought, as I start to try this simplifying… I suspect I can make the query work, but I also beleive the shape of the data will be exactly the opposite what I want, and it may make for some very ugly code manually constructing structs or something to rearrange data how I want… I hoped the group_by clause in Ecto would flip things around to hand me back the CreditType structs I want, with the stocks and foods for them loaded up, but I’m pretty sure that was a delusion on my part because I didn’t understand group by correctly or something… is there anyway, given a bunch of sql data, to control how ecto instantiates the structs out of it? I start this query with from stocks in Stock, and so I’m pretty confident what I get out will be a list of Stock structs, but thats not what I want.. I want a list of CreditType, containing the stocks as a relation… I sort of lost focus on that while thinking about how to reproduce this SQL query as Ecto, but without that, I think I have to, in memory in elixir, do all kinds of weird acrobatics with the stocks that come back, shoving them into a map or struct based on which credit type’s they have present… which I can do, but just confirmed there’s not a better way.
0
bglusman
that wasn’t done as a reply to any of you so you may not see it.. @OvermindDL1 / @cmkarlsson / @kip
0