Use Condition of SQL

When I first RoR programming, I was heavily dependent on Ruby language and Rails framework, so when searching for key on google I always added a keyword “ruby” or “rails”. At that time, mysql was really just a “data store” and not “a database management system”.

When encountering a potato problem about retrieving data, I will always search google with the keyword “rails” to see if there are any results related to the model, and if there is no workaround then: “Wow, this way. You can be indifferent! “.

It was a huge mistake of mine, because I completely ignored the cool features of SQL and followed the rather rigid things of the programming language. The following article I will present a tip I’ve just learned from a lead brother, I’m not sure if it is good or not, is it new to everyone, but until now, it helped me. A lot of hope and hope that it can help everyone in a certain case (in case the project rules allow writing this way).

So what’s the tip here?

Have you ever encountered a situation like this or similar?

You have 1 table but there are 2 date type fields to store 2 different date types (if a record has date_a, there is no date_b and vice versa) and the spec must sort both of those tables at the same time as ASC or DESC as in the example. after:

We have the User table has 2 fields date_a and date_b:

And the spec forces us not to sort like this:

query:

result:

as we see 04-06 less than 26-08 but below because its date_a column is simply nil. And the above query means to order date_a first if two or more records with the value of date_a are equal, then date_b:: asc is really valid (it will sort the children with equal date_a based on date_b)

But for example the customer says “No, I don’t like it, I want it to sort both at the same time like this:”

“That’s very difficult”, if you search google with the keyword “rails”, then it will make sense or if there is another way, it will be ambiguous. So now is the time to use SQL, but what else? I believe most people will think of SQL right away: D

So the problem is how to write to sort as above?

I used the conditional functions of SQL (IF ELSE, CASE WHEN), which at that time I thought “Eh SQL has IF ELSE?”:

In addition, I have added a flag column to use the IF control (if flag = true, use date_a, flag = false, then use date_b to sort). So now we have the following table:

and the first 1 scope as follows:

The command above will produce satisfactory results for customers. But you should use Arel and change it like this.

result:

We have the following query:

And there is another great example of IF ELSE too:

We have the following 2 tables:
User:

Payment:

And we have to display the table in the view as follows:

Normally we will use the render collection on each row user, and each user we write a function to calculate the total amount, and a function to calculate the total amount paid to call outside the view.
And the result is sure to be 2n + 1 query (1 query total money, 1 query total paid by each user).

Some of you experienced “old projection” will immediately think of writing a scope when placed on the controller to resolve the n + 1 query column “Total amount” as follows:

And call back on controller:

and out of view we just need to call

is solved the “total money” column is not queryed again.

What about “paid money”, potato spreads because it must have a paid = true field. Should you have to write another scope to add where paid = true, then join again with the User? That’s too crazy! Is that okay?

We will use IF ELSE in the scope just now:

and out of call view

So that solves all n + 1 queries

References

https://www.mysqltutorial.org/mysql-if-statement/