Include field names when using `query`.

[joshuapinter]

Something we noticed after upgrading from mysql2 to trilogy was the lack of field names when running a query manually.

Here is a simple example of the differences:

mysql2

Mysql2::Client.new( config ).query( "SELECT * FROM instruments" ).first
#=> {"SerialNumber"=>"ABCD1234", "Manufacturer"=>"Thermo", "Model"=>"TVA2020"}

trilogy

temporary_database_connection = Trilogy.new( config )
temporary_database_connection.select_db( config[ "database" ] )
temporary_database_connection.query( "SELECT * FROM instruments" ).first
#=> ["ABCD1234", "Thermo", "TVA2020"]

Differences

A few differences to point out:

1. In order to establish a database connection and run a query, you need to create a new Trilogy instance and then use select_db or change_db and provide the database name again, otherwise, you get a "No database selected" error.

2. mysql2 returns a Hash while trilogy returns an Array from the query method.

3. In an otherwise incredible drop-in replacement, this prevents the usage of fetching the row values based on the column names, like this:

   temporary_database_connection.query( "SELECT * FROM instruments" ).first.fetch( "SerialNumber" )

   Which worked very well on mysql2.

We're reverting back to mysql2 in the meantime but thought I would post it here to get your input because this is (so far) the only thing preventing us from moving forward with trilogy.

Many thanks!

Joshua

[composerinteralia]

~Related: https://github.com/trilogy-libraries/trilogy/issues/99~ Actually maybe not that related on second read.

[composerinteralia]

Not the same as mysql2, but temporary_database_connection.query( "SELECT * FROM instruments" ) does return a Trilogy::Result, which has #fields available. So as an immediate workaround you could do something like:

result = temporary_database_connection.query( "SELECT * FROM instruments" )
result.rows.map { |row| result.fields.zip(row).to_h }

to get hashes.

[joshuapinter]

Very true, and that's a great use of zip!

We use this in quite a few places so it would mean changing a lot of lines, minor as it is.

Is there a desire to have rows return a Hash with the fields as keys or is that not going to work? If so, then we'll help with that before moving to trilogy. If not, then we'll go this workaround route at some point.

Let me know. And thanks again!

[jhawthorn]

Trilogy isn't a intended as a 1:1 drop in replacement for mysql2, though it is similar due to shared authorship and consumers.

I don't think it's a good idea for us to change the behaviour of rows as that would break backwards compatibility. I think you can achieve what you want using result.each_hash.first

[composerinteralia]

Oh right, I totally forgot about each_hash. Thanks John. Yeah, I agree with closing this.

[joshuapinter]

Totally fair. I'll give that a shot and go from there. Thanks for all your work on this.

[joshuapinter]

Just wanted to say, .each_hash.with_index( 1 ) do |row, row_number| works a treat!

And to get a single expected row, I ended up using .each_hash.sole. This worked great as it returned a Hash with the field names but also would raise Enumerable::SoleItemExpectedError: multiple items found if there were multiple results from the query, which is a nice check to ensure you're getting what you expect.

I didn't know each_hash existed. Is there a good place to find or add some documentation for that?

Many thanks!

[composerinteralia]

Our documentation is limited at the moment, but we do have https://github.com/trilogy-libraries/trilogy/blob/3c3a3865a3896f3056b1d7c69c5244964b60937c/contrib/ruby/README.md?plain=1#L33. The method is implemented in https://github.com/trilogy-libraries/trilogy/blob/3c3a3865a3896f3056b1d7c69c5244964b60937c/contrib/ruby/lib/trilogy/result.rb#L9-L25

[joshuapinter]

Yup, perfect.

https://github.com/trilogy-libraries/trilogy/tree/main/contrib/ruby#usage

That would have been super helpful. You even callout that trilogy returns the row values with .each, and to use .each_hash if you want the column names as well:

There is no as query option. Calling Trilogy::Result#each will yield an array of row values. If you want a hash you should use Trilogy::Result#each_hash.

Thanks again.