CODEWITHSUNDEEP
phpmysqlsqllaraveleloquent
Michael J. Calkins
Michael J. Calkins

Reputation: 32655

Select Last Record With Value With Eloquent

I'm storing fields and values in a key value style table. I want to store revisions of user data over time. When I select from their data I only want the latest value of each key.

http://sqlfiddle.com/#!2/d7138

enter image description here

I'm currently eager loading but this selects all keys in this array when I just want the last value for each key.

    public function healthProfile()
    {
        return $this->hasMany('PortalUserMember', 'portal_user_id')
            ->whereIn('key', [
                'health.profile.sex',
                'health.profile.birthday_day',
                'health.profile.birthday_month',
                'health.profile.birthday_year',
                'health.profile.height_ft',
                'health.profile.height_in',
                'health.profile.weight_lbs',
                'health.profile.contact_street_1',

                            // Could be anything at any point.

                'health.profile.mail_pharmacy_name',
                'health.profile.mail_pharmacy_fax',
                'health.profile.mail_pharmacy_phone'
            ]);
    }

Update

I'm doing this as a temporary work around:

http://laravel.io/bin/5zn58

Upvotes: 0

Views: 111

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

http://sqlfiddle.com/#!2/d7138/5

SELECT `key`, value FROM portal_user_members pum1
WHERE portal_user_id = 1
AND `key` IN  ('health.profile.sex',
'health.profile.birthday_day',
'health.profile.birthday_month',
'health.profile.birthday_year',
'health.profile.height_ft',
'health.profile.height_in',
'health.profile.weight_lbs',
'health.profile.contact_street_1',
'health.profile.mail_pharmacy_name',
'health.profile.mail_pharmacy_fax',
'health.profile.mail_pharmacy_phone')
AND id = (SELECT MAX(id) 
FROM portal_user_members pum2
WHERE pum2.key = pum1.key)

Another version using GROUP BY. This may be faster depending on how you've indexed your tables. http://sqlfiddle.com/#!2/d7138/9

SELECT pum1.key, pum1.value 
FROM portal_user_members pum1
JOIN (
SELECT `key`, MAX(id) id
FROM portal_user_members pum2
WHERE portal_user_id = 1
AND `key` IN  ('health.profile.sex',
'health.profile.birthday_day',
'health.profile.birthday_month',
'health.profile.birthday_year',
'health.profile.height_ft',
'health.profile.height_in',
'health.profile.weight_lbs',
'health.profile.contact_street_1',
'health.profile.mail_pharmacy_name',
'health.profile.mail_pharmacy_fax',
'health.profile.mail_pharmacy_phone')
GROUP BY pum2.key  
) pum2 ON pum2.id = pum1.id

Upvotes: 1

Related Questions