SQL Select if else

Question

I need some help.. I need to output the value of DisplayName if the value of fullname return a null or empty as FullName.

something like

if(u.first_name && u.last_name are not empty)
    output CONCAT(u.first_name,' ',u.last_name) 
else
    output max(case when t.field_id = 1 then t.value else '' end)

but how? also my query took 3.45 seconds :(..

SELECT * FROM(  
    SELECT
          t.user_id,

##this code
max(case when t.field_id = 1 then t.value else '' end) DisplayName,
CONCAT(u.first_name,' ',u.last_name) FullName,

          max(case when t.field_id = 270 then t.value else '' end) Gender,
          max(case when t.field_id = 274 then t.value else '' end) Birthday,
          max(case when t.field_id = 274 then FLOOR(DATEDIFF(CURRENT_DATE, STR_TO_DATE(t.value,'%m/%d/%Y'))/365)  else '' end) Age,
          max(case when t.field_id = 275 then t.value else '' end) Phone,
          max(case when t.field_id = 286 then t.value else '' end) Email,
          max(case when t.field_id = 71 then t.value else '' end) Occupation,
          max(case when t.field_id = 73 then t.value else '' end) CurrentCountry,   
          max(case when t.field_id = 24 then t.value else '' end) Region,   
          max(case when t.field_id = 355 then t.value else '' end) Province,   
          max(case when t.field_id = 354 then t.value else '' end) City  
    FROM wp_bp_xprofile_data t 
    LEFT JOIN (
        SELECT
        user_id id,
        max(case when meta_key = 'first_name' then meta_value end) first_name,
        max(case when meta_key = 'last_name' then meta_value end) last_name
        from  wp_usermeta  
        GRoup by user_id

    )as u
    ON u.id = t.user_id  
    GROUP BY user_id
)temp 
WHERE 1 = 1  
##Some Condition to be added 
LIMIT 0 , 30

DRapp · Accepted Answer

I would change to a multiple join to same table repeatedly to get each field on its own criteria as a possibility of performance. Start the profile data one for say the field_id = 1 as the basis, then join all the rest on that same key, then no grouping by or max/case when on every tested row. IF some fields may NOT exists, just have as LEFT-JOIN to the re-used table with alternate alias. I would ensure you have a compound/covering index on your profile table with the following key

( user_id, field_id, value )

Then do the following query. The interesting thing here, is it basically opens the same table for each "component" part of the user_ID and gets them all lined up, but runs the query based only on a single instance of records associated with field_id = 1 in the where clause. All the other instances are in their respective aliases joined ON clause. So now, I just run through the records for field_id = 1, and if found on the other tables, grabs the value, otherwise blank (via coalesce). Similarly joined to the user meta-data table to get the first/last name parts.

if this works for you, I would be interested in knowing the performance one-way or the other. The benefit is that it doesn't have to query EVERYONE up front (the inner-most query) to then run the main query of all records, apply the group by, then stop at your 30 limit clause. It just gets up to the first 30 where field_id = 1 and its done. Now, if the population of "field_id = 1" is a low populated field, and you want to swap with another, so be it, you know your data better than us.

SELECT
      DN.User_ID,
      case when fn.user_id is null and ln.user_id is null then DN.value 
           when fn.user_id is null then ln.meta_value
           when ln.user_id is null then fn.meta_value
           else concat( fn.meta_value, ' ', ln.meta_value ) end as FinalName,
      DN.Value DisplayName,
      CONCAT( COALESCE( fn.meta_value, '' ),' ',COALESCE( ln.meta_value, '' )) FullName,
      COALESCE( G.Value, '' ) Gender,
      COALESCE( BD.Value, '' ) Birthday,
      COALESCE( FLOOR( DATEDIFF( CURRENT_DATE, STR_TO_DATE( BD.value,'%m/%d/%Y'))/365), '' ) Age,
      COALESCE( P.Value, '' ) Phone,
      COALESCE( E.Value, '' ) Email,
      COALESCE( O.Value, '' ) Occupation,
      COALESCE( CC.Value, '' ) CurrentCountry,
      COALESCE( R.Value, '' ) Region,
      COALESCE( PR.Value, '' ) Province,
      COALESCE( C.Value, '' ) City
   from
      wp_bp_xprofile_data DN
         LEFT JOIN wp_bp_xprofile_data G
            ON DN.User_ID = G.User_ID AND G.field_id = 270
         LEFT JOIN wp_bp_xprofile_data BD
            ON DN.User_ID = BD.User_ID AND BD.field_id = 274
         LEFT JOIN wp_bp_xprofile_data P
            ON DN.User_ID = P.User_ID AND P.field_id = 275
         LEFT JOIN wp_bp_xprofile_data E
            ON DN.User_ID = E.User_ID AND E.field_id = 286
         LEFT JOIN wp_bp_xprofile_data O
            ON DN.User_ID = O.User_ID AND O.field_id = 71
         LEFT JOIN wp_bp_xprofile_data CC
            ON DN.User_ID = CC.User_ID AND CC.field_id = 73
         LEFT JOIN wp_bp_xprofile_data R
            ON DN.User_ID = R.User_ID AND R.field_id = 24
         LEFT JOIN wp_bp_xprofile_data PR
            ON DN.User_ID = PR.User_ID AND PR.field_id = 355
         LEFT JOIN wp_bp_xprofile_data C
            ON DN.User_ID = C.User_ID AND C.field_id = 354
         LEFT JOIN wp_usermeta FN
            ON DN.User_ID = FN.User_ID AND FN.meta_key = 'first_name'
         LEFT JOIN wp_usermeta LN
            ON DN.User_ID = LN.User_ID AND LN.meta_key = 'last_name'
   where
          DN.field_id = 1
      AND O.Value like '%Programmer%'
   LIMIT 
      0, 30

I modified to add a FINAL Name by use of a case/when. if both the alias FN/LN (first name / last name) are BOTH null, then get the DN (display name) value. Otherwise we have AT LEAST ONE or BOTH parts of the first/last name field. If the first name was null, just return the last name. If the last name was null, return the first name, otherwise BOTH first and last names were available, and return the concatinated version of the name.

As for applying extra WHERE clauses. As soon as you add a "required" criteria to the where clause against any of the left-join aliases, it will in-effect turn them into INNER JOINs and only include those that HAVE that component AND that component has the value you want... So, per your comment I would just add...

   where
      DN.field_id = 1
      AND BD.User_ID IS NOT NULL
      AND STR_TO_DATE( BD.value,'%m/%d/%Y') BETWEEN '2009-01-01' and '2012-01-01'

You could use your datediff for age and do that between 1 and 2, but just a preference to pick a date range of interest..

For your additional comment about Programmer filtering, the way you had it was trying to test ALL the joined aliases looking for programmer, but at the same time, all of them required because you wanted them all as NOT NULL. If you want a field required, but no other specific criteria, such as ALWAYS must have email address, then just change

LEFT JOIN wp_bp_xprofile_data E

to

JOIN wp_bp_xprofile_data E

if you want to make sure the field is considered only if it has a value other than space (such as your last/first name)

   LEFT JOIN wp_usermeta FN
      ON DN.User_ID = FN.User_ID 
      AND FN.meta_key = 'first_name' 
      AND LENGTH( TRIM( COALESCE( FN.meta_Value, '' ))) > 0

This will make sure the record is only considered when it actually has a non-empty string value.

Back to your consideration of "%Programmer%'... why would you ever expect a city, Province, region, etc to have a value of programmer. You should only require the Occupation instance to have the value. Also, because you had an OR, it was doing ALL the rows. You would need to have wrapped the conditions something like

   where
          DN.field_id = 1
      AND (   O.Value like '%Programmer%'
           OR E.Value like '%@gmail.com'
           OR C.Value like 'Some City' )

Notice I wrapped my ADDITIONAL criteria OR components. This way it still only starts with the Field_ID = 1 entries, but based on the JOIN adds the extra criteria, and in this case, DN.field_id = 1 always, but ANY ONE (or more) of the OR conditions must also be true... The Occupation alias has a value like programmer, email alias has a value of gmail, the city alias like 'Some City'.

Hopefully this clarifies how to implement... Also, make note, that using any LIKE qualifier that has '%' leading the string needs to compare through the whole string and will slow the query down some, but since the join is based on the user ID and the field_ID value, that should still keep it fast (relatively).

SQL Select if else

Answers (2)

Related Questions