CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
sclem72
sclem72

Reputation: 486

Appending strings to SQL join tables only when that field is present

I have a User model, being joined with the profile model to create a join table to hold all of the information belonging to each model. I want to be able to append a string to the end of the value of a column, but only when that field is present. Here is what I currently have

User.joins(:profile).select('users.id,
                            full_name AS name,
                            concat(profiles.address1, profiles.address2) AS address,
                            profiles.state,
                            profiles AS phone_number')

Which will give me:

id    |    Name    |    Address    |    State    |    Phone Number
1     | Bob Smith  | 123 Bakery Ln |     CA      |    123-456-7890
2     | Linda Joe  | Apt 51        |     AZ      |    098-765-4321
3     | Steve Jobs | 1 Apple Drive |     CA      |    321-654-0987

But notice that Linda Joe's address is just her apartment number, here is how the object looks: (User.find(2).profile.address1 == nil && User.find(2).profile.address2 == 'Apt 51') == true.

I would like to find a way to only concatenate address only if address1 is present. I would like for it to look like this instead: 

id    |    Name    |    Address    |    State    |    Phone Number
1     | Bob Smith  | 123 Bakery Ln |     CA      |    123-456-7890
2     | Linda Joe  |               |     AZ      |    098-765-4321
3     | Steve Jobs | 1 Apple Drive |     CA      |    321-654-0987

Any help is appreciated. Thanks!

Upvotes: 1

Views: 247

Answers (1)

MurifoX
MurifoX

Reputation: 15089

You can use the CASE statement and do a conditional concatenation inside your select: 

User.joins(:profile).select('users.id,
                        full_name AS name,
                        CASE WHEN profiles.address1 IS NOT NULL
                        THEN concat(profiles.address1, profiles.address2)
                        ELSE '' END AS address,
                        profiles.state,
                        profiles AS phone_number')

Upvotes: 1

Related Questions