I am not clear yet on the proper way to run raw SQL queries with Sequel.
Currently I am trying this:
DB.fetch("SELECT * FROM zone WHERE dialcode = '#{@dialcode}' LIMIT 1") do |row|
@zonename = row
end
How can I can run the queries as raw SQL then access the results like normal?
if @zonename.name = "UK"
You can include multiple SQL statements on the SQL query panel. The exceptions are CALL and CREATE PROCEDURE statements. These statements must be used alone in a query.
As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the sequelize. query method. By default the function will return two arguments - a results array, and an object containing metadata (such as amount of affected rows, etc).
Subqueries must be enclosed within parentheses. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.
Note that instead of:
DB.fetch("SELECT * FROM zone WHERE dialcode = '#{@dialcode}' LIMIT 1")
you should do:
DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode)
Otherwise, you open yourself to SQL injection if you don't control the contents of @dialcode
.
I have a few pointers which may be useful:
You could simply do:
@zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).first
NB: you are ignoring the fact that there could be more results matching the criteria. If you expect multiple possible rows to be returned then you probably want to build an array of results by doing ...
@zonename = DB.fetch("SELECT * FROM zone WHERE dialcode = ? LIMIT 1", @dialcode).all
and processing all of them.
The return set is a hash. If @zonename
points to one of the records then you can do
@zonename[:column_name]
to refer to a field called "column_name". You can't do @zonename.column_name
(you could actually decorate @zonename
with helper methods using some meta-programming but let's ignore that for the moment).
Sequel is an excellent interface, the more you learn about it the more you'll like it.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With