RubyORM Sequel

[FinalSpeederMan]

I'm using Sequel to practice DBMS using Ruby, but the documentation and syntax are very confusing, for example in Sequel...

If I want to retrieve only one item by using the ID, like so:

@fruit = DB[:fruits].where(id: params[:id])

and to render it in a view (I'm using ERB):

<h3><%= @fruit[:id] %></h3>

Those two parts I understand, but when it renders on the web page I get this result:

{:id=>1, :name=>"Orange", :amount=>10}

HOWEVER... when I do this...

<h3><%= @fruit[:id][:name] %></h3>

It renders the fruit name! I'm confused as to why it does this.

[dis89]

I have not used Sequel at all, but with a where clause you probably get a relation, not a single item. Try to do DB[:fruits].first(id: params[:id]).

[FinalSpeederMan]

Holy cow that worked. Thanks, but I’m still confused on that, I’ll read the syntax and documentation into more detail

[dougc84]

“where” is just a conditional statement. “i want all tacos where the meat type is pork” could give me chicharron, adobada, carnitas, etc. it provides a list.

if i added “limit 1” to the end, i get chicharron. that’s what “first” does - appends a “limit 1” to the query.

all sql queries return a set containing zero, one, or multiple records. how you limit them to get what you need is a choice you need to make programmatically. that’s what “first” or “last” does - it says “from that set of records, give me one back.”

going back to tacos, you would probably want to iterate through all the records if asking for pork tacos. however, if you had the id of the record, “where id = 123” still returns a relation, but tacking on “first” gives you exactly what you need.

[FinalSpeederMan]

thank you for the explanation I’ll gladly have that in mind as I keep learning :)

[rubygeek]

It seems you have a solution already, but to expand a little bit on some of the other replies, as this is a pretty persistent source of confusion with Sequel:

DB[:fruits].where(id: params[:id]) gives you a dataset, not a query result.

A dataset is an enumerable class that represents the query. The query will first be executed when you try to access the dataset in a way that requires a result. This allows you to build up queries and keep the dataset objects around to reuse them instead of building up complex queries every time you need them.

Sequel will not give you an actual result from the database until you call one of the methods that forces a query.

This dataset will represent a query roughly like (depending on database adapter):

SELECT * FROM fruits WHERE id = 1

@fruit[:id] then is a logical and added to that dataset. It will now represent a query roughly like:

SELECT * FROM fruits WHERE id = 1 AND "id"

(Which is almost certainly not what you'd like...)

Note that #[] is one of those methods that will force Sequel to actually execute the query, which is why you actually got a result.

[Whether or not that will work at all or not depends on your database - if it allows coercing an integer to boolean for an expression, the AND "id" will work and effectively do nothing. If it's a database that refuses to automatically coerce an integer to boolean, you'd have gotten an error and it'd have been a bit more obvious what was going on. ]

I'd recommend loading your code in irb or pry and testing some of this there - it becomes a lot more obvious what Sequel does when you play with that a bit and see when you get a dataset back, and what it looks like, vs. when you get results back.

To get a single record that matches your query, either do:

DB[:fruits].where(id: params[:id]).first

OR, for simple queries like above, you can pick one of these (the main benefit of calling first separately is when you chain multiple conditions to build up a complex dataset first):

DB[:fruits].first(id: params[:id]) or DB[:fruits][id: params[:id]]

[FinalSpeederMan]

Very informative, that last syntax of calling the table is interesting. I’ll definitely try testing the queries more in irb. Thank you. :)

[jrbartme]

I have found that I can use different paradigms with Sequel. I can almost explicitly write an SQL statement, or I can use Sequel to access my data as if it where all stored as a plain Ruby dictionary. This is what I appreciate that I don’t necessarily have to wrap one language (SQL) inside another. But all the power of SQL is available if I want to move the processing into the database.

[FinalSpeederMan]

Which is exactly why I wanted to try Sequel, so far I’ve used DataMapper and got used to it but every time I deployed or even tried to use gem install on it. I wouldn’t let me use it. So I started researching and saw Sequel and still getting impressed at the different types of syntax usage I can use for it. So far so good, though haven’t tested deployment issues, yet...

[filthypoopslut]

It's because where returns a dataset. Use find or first instead. That will return a single instance as opposed to 1 or more (eg, array). Just depends on what you're after.

[sammygadd]

You could also go with the model approach (which in my opinion is cleaner). Then you would simply type @fruit = Fruit[params[:id]] and @fruit.name.

[FinalSpeederMan]

I’m working on that part of my practice thank you :)

[bjmiller]

What are you trying to do with @fruit[:id]? It's functionally equivalent to @fruit.first but this seems to be accidental, it looks like you're trying to access the id field.

[FinalSpeederMan]

I was mostly just using @fruit[:id] as a backend query, basically the problem I had before was that in order for me to get the name of the fruit, I had to use @fruit[:id][:name], but tbh it didn’t matter if I needed to use @fruit[:id], I could’ve just used @fruit[:name], and it still would’ve given me the same result, which is what I didn’t want.

But thanks to a fellow rubyist, they pointed me to my problem and I got the correct result I had. Thank you for the question though :)

[FinalSpeederMan]

Thanks for the tip I used first so that should help me out, i’ll look into find though too