Customizing the generated SELECT in a query? [Granite ORM]

Question

I'm trying build a JOIN query in Amber (using the Granite ORM) on a legacy database (with existing data & table structure), and wondering if it's possible to customize the SELECT FROM portion of the query to support a cross-table JOIN.

Here's the current table structure for a table called vehicles:

-----------------------------------------------
| vehicleid | year | makeid | modelid |
-----------------------------------------------
| 1 | 1999 | 54 | 65 |
| 2 | 2000 | 55 | 72 |
| ... | ... | ... | ... |
-----------------------------------------------

etc.

Where makeid and modelid are foreign key references to makes and models table. In those tables are naming columns (makename and modelname, respectively).

I'm trying to generate a JOIN query to pull in the names:

SELECT vehicle.yearid, make.makename AS make, model.modelname AS model FROM vehicles JOIN....

(snipping out the JOIN details).

So that when the query returns I have a single Vehicle object and can access:

Vehicle.yearid,

Vehicle.make, and

Vehicle.model

Is this possible using Granite?

I can get the JOIN portion of the query to generate by using raw SQL, but I can't figure out how to customize the table & column names in the SELECT portion. I've tried creating an object as so:

class Vehicle < Granite::ORM::Base
  adapter pg

  primary vehicleid : Int32
  field yearid : Int32
  field make : String
  field model : String
end

But Granite is generating the following SQL:

SELECT vehicle.yearid, vehicle.make, vehicle.model FROM vehicle JOIN...

That's throwing an error because vehicle.make and vehicle.model don't actually exist.

what I want is this SQL:

SELECT vehicle.yearid, make.makename AS make, model.modelname AS model FROM vehicles JOIN....

Is there a way to make this work?

Sven · Answer 1 · 2018-04-23T23:59:08.840

According to this issue, Granite does not yet have one-to-one relationships, but the author mentions that there is a temporary workaround by using the has_many macro and define a method which calls the method defined by the macro but returns the first element in the Array returned by that method (since it can only be one element).

First you need to create models for the two other tables, model and make:

class Model < Granite::ORM::Base
  adapter pg

  belongs_to :vehicle

  primary modelid : Int32
  field modelname : String
end

class Make < Granite::ORM::Base
  adapter pg

  belongs_to :vehicle

  primary makeid : Int32
  field makename : String
end

If you have more fields than just modelname or makename, be sure to add those as well.

And lastly, you need to add has_many relationships to the original Vehicle class, and define make and model methods:

class Vehicle < Granite::ORM::Base
  adapter pg

  primary vehicleid : Int32
  field yearid : Int32

  has_many :makes
  has_many :models

  def make
    makes.first["makename"]
  end

  def model
    models.first["modelname"]
  end
end

Querying is then as simple as:

vehicle = Vehicle.find 2

puts vehicle.model

Unfortunately I don't believe that Granite yet supports column aliases (AS) without completely bypassing the ORM, so you have to return those columns explicitly (which the code above does) or access the property directly with vehicle.model["modelname"].

Note: I may have gotten the types of the Hash returned by Granite wrong, since their source-code hasn't got any type annotations and fully relies on Crystal's type inference, which makes it hard to navigate. But I think it is {} of String => DB::Any, but I could be wrong. If you get a compiler error, try with a Symbol instead of String.

This is a great and helpful answer - thank you! Unfortunately it doesn't quite solve this particular problem as the current implementation of has_many and belongs_to are fixed in their naming (the foreign key must be in the form of make_id and model_id -- with the underscore). But I gave it an upvote and appreciate your adding this info as I'm certain it'll be helpful for others. I'll need to figure out a solution without relying on the relation helpers. — nlh, Apr 24 '18 at 03:45
@nlh Oh that's a bummer. Since this seems to be an edge-case, couldn't you copy their macro implementation and change it? Something like this should fix it: https://gist.github.com/Svenskunganka/9458ed05097b5bcbeb0ecdd4ed2acd77 — Sven, Apr 24 '18 at 04:24
That's a great idea - thank you! I ended up taking a slightly different (but equally monkey-patchy) route as you were writing this answer. Will share in a separate answer shortly... — nlh, Apr 24 '18 at 05:32

score 4 · Answer 2 · answered Apr 24 '18 at 05:42

Thanks to @svenskunganka for giving me an idea to think this route, I came up with a solution in the spirit of Granite that stays close to raw SQL and lets the ORM stick to mapping fields to object.

I added a sql class method to the model definition that behaves almost identically to all but strips away a bit more structure. I also had to add a new query method to the pg adapter in order to support it, but this now works for my use case. Here's the monkey-patched code:

class Granite::Adapter::Pg < Granite::Adapter::Base
  def query(statement = "", params = [] of DB::Any, &block)
    statement = _ensure_clause_template(statement)
    log statement, params
    open do |db|
      db.query statement, params do |rs|
        yield rs
      end
    end
  end
end

module Granite::ORM::Querying
  def sql(clause = "", params = [] of DB::Any)
    rows = [] of self
    @@adapter.query(clause, params) do |results|
      results.each do
        rows << from_sql(results)
      end
    end
    return rows
  end
end

It's a bit ugly (and am open to suggestions for cleaning this up) but I can now write the following code:

vehicles = Vehicle.sql("SELECT vehicle.vehicleid, vehicle.yearid, make.makename AS make, 
model.modelname AS model FROM vehicle JOIN ...<snip>")

I can then do something like:

vehicles.each do |v| 
  puts "#{v.yearid} #{v.make} #{v.model}"
end

And it works as-expected.

Customizing the generated SELECT in a query? [Granite ORM]

2 Answers2