What kind of queries are not working with Avram?

[paulcsmith]

SQL can get pretty wild! Sometimes Avram can’t get you what you want, but we’d like to be able to handle most queries out of the box.

Please post here with any queries that require custom SQL so we can try to get them working :D

[paulcsmith]

From @confact

def get_pages
    sql = <<-SQL
    SELECT path as address, COUNT(id) as count FROM events
    WHERE domain_id=#{@domain.id} AND created_at > '#{period_days}'
    GROUP BY path
    ORDER BY COUNT(id) desc LIMIT 10;
    SQL
    pages = AppDatabase.run do |db|
      db.query_all sql, as: StatsPages
    end
    pages = count_percentage(pages)
    return pages
  end

[paulcsmith]

Bonus points if you also include an example in another library where the query would work. That way we can see how others handle it. Thank you!

[jwoertink]

SELECT name FROM users;

# from rails
User.select(:name)

[paulcsmith]

Is this done for performance reasons? I typically do UserQuery.new.map(&.name) but maybe that doesn't work for all use-cases? Would love to learn more about this

[jwoertink]

Yeah, for performance reasons, but also in the case of dynamic columns which becomes a whole other topic. For example: In rails, you can do

user = User.select("COUNT(friends) as how_many_friends_do_i_have").first
user.how_many_friends_do_i_have #=> 2

I don't see how we would be able to implement this in a type safe way, but I figured it should at least be documented in case one of us has that "lightbulb" moment some day 😅

[jkthorne]

would UserQuery query the entire row and then select name. I think the Rails version only returns the field name not the whole row.

[confact]

Oh, one more:

SELECT COUNT(DISTINCT user_id) FROM events WHERE domain_id=#{@domain.id} AND created_at > '#{period_days}';

# from rails:
Event.where("created_at > ?", period_days).where(domain_id: @domain.id).distinct.count(:user_id)

[paulcsmith]

@confact I think this would work with this:

period_days = 5.days.ago # or minutes, seconds, whatever
EventQuery.new.created_at.gt(period_days).domain_id(@domain.id).distinct_on(&.user_id).select_count

Does that work? If not can you let me know what the issue is or open an issue? Because this should be working now

[matthewmcgarvey]

Queries using joins can produce invalid sql luckyframework/avram#370

[paulcsmith]

Ruh-roh! Well we definitely want to fix that :D