4

Can anyone provide an SQL command that retrieves a customer's first and last name in one row? All my attempts at JOINs have resulted in multiple rows, with first name in one row and last name in the next.

4
  • 4
    Models in Magento exist for a reason - you shouldn't be interacting directly with the DB. Commented Feb 4, 2013 at 23:41
  • 3
    There are a number of reasons to deal with the SQL directly. Suffice to say, this is happening completely outside the Magento framework.
    – Ryre
    Commented Feb 4, 2013 at 23:44
  • 2
    For external requests, the API serves to allow peripheral communication. Be it XML-RPC/RESTful. There is no harm merely doing SELECTS. But as a module/integration grows - there is an increasing likelihood that you'll need to do an UPDATE/INSERT - and you'll either end up doing direct DB manipulation; having a messy module with a blend of direct SQL calls and API calls or having to rewrite your code. Commented Feb 5, 2013 at 15:08
  • I think this question should probably be tagged with debugging or something that better describes why one would want to be performing direct SQL queries. I could see simple debugging purposes, but outside of that, there is really no reason to be doing this, and without clarification, it sends the wrong message to newer developers.
    – pspahn
    Commented Feb 6, 2013 at 6:40

6 Answers 6

7

Since attribute ids for first and last name vary between magento instlalations it would be wise to select attribute_id values for first name and last name from eav_attribute and write the query for retrieving first and last name in a single row like this:

SELECT entity_id, group_concat(VALUE SEPARATOR ' ') AS fullname 
FROM customer_address_entity_varchar AS val
INNER JOIN eav_attribute AS attr ON attr.attribute_id  = val.attribute_id
WHERE attr.attribute_code IN ( 'firstname',  'lastname' ) 
GROUP BY entity_id
12

As @sonassi pointed out, you should really be using Magento models to interact with the data. I would add that regardless of where your code is, this still holds true. If you are writing something completely detached from Magento, then use the Web Services API, it's what it's there for.

You can get a collection of customers in Magento with the full name loaded on the individual records by calling addNameToSelect on the collection before loading it:

$collection = Mage::getResourceModel('customer/customer_collection')
    ->addNameToSelect()
    ->setPageSize(20)
    ->load()
;

With the above having been run, you can then call $collection->getSelect()->assemble() to get the query which Magento uses:

SELECT
    `e`.*,
    `at_prefix`.`value` AS `prefix`,
    `at_firstname`.`value` AS `firstname`,
    `at_middlename`.`value` AS `middlename`,
    `at_lastname`.`value` AS `lastname`,
    `at_suffix`.`value` AS `suffix`,
    CONCAT(IF(at_prefix.value IS NOT NULL AND at_prefix.value != '',
                CONCAT(LTRIM(RTRIM(at_prefix.value)), ' '),
            ''),
            LTRIM(RTRIM(at_firstname.value)), ' ',
            IF(at_middlename.value IS NOT NULL AND at_middlename.value != '',
                CONCAT(LTRIM(RTRIM(at_middlename.value)), ' '),
            ''),
            LTRIM(RTRIM(at_lastname.value)),
            IF(at_suffix.value IS NOT NULL AND at_suffix.value != '',
                CONCAT(' ', LTRIM(RTRIM(at_suffix.value))),
            '')
        ) AS `name`
FROM `customer_entity` AS `e`
    LEFT JOIN `customer_entity_varchar` AS `at_prefix` ON (`at_prefix`.`entity_id` = `e`.`entity_id`) AND (`at_prefix`.`attribute_id` = '4')
    LEFT JOIN `customer_entity_varchar` AS `at_firstname` ON (`at_firstname`.`entity_id` = `e`.`entity_id`) AND (`at_firstname`.`attribute_id` = '5')
    LEFT JOIN `customer_entity_varchar` AS `at_middlename` ON (`at_middlename`.`entity_id` = `e`.`entity_id`) AND (`at_middlename`.`attribute_id` = '6')
    LEFT JOIN `customer_entity_varchar` AS `at_lastname` ON (`at_lastname`.`entity_id` = `e`.`entity_id`) AND (`at_lastname`.`attribute_id` = '7')
    LEFT JOIN `customer_entity_varchar` AS `at_suffix` ON (`at_suffix`.`entity_id` = `e`.`entity_id`) AND (`at_suffix`.`attribute_id` = '8')
WHERE (`e`.`entity_type_id` = '1') LIMIT 20

As you can see, it is quite a beast, and I will re-iterate what I opened with. Unless you are using this for manually introspecting the data you should be using the Magento models to interact with the data. Attempts at avoiding them will almost guarantee that you break compatibility with varying versions, as stuff like this does change. The immediate issue though, you cannot rely on the values used for the attribute_id values being the same across the board. Most of the time they will be due to them being setup by install scripts, but I have seen cases where they are not what would be called "normal."

1
  • 2
    And what would you call the massive query which I not only included in my answer, but also directed you on how to find it? Why don't you, instead of revolting at the warnings, try the SQL which I posted. It works!
    – davidalger
    Commented Feb 5, 2013 at 19:40
2

Here it is for name and email, attribute_id might not be 5 so change it if needed..

SELECT cev.value AS first_name,  ce.email AS email
FROM  `product_alert_stock` AS pas
LEFT JOIN  `customer_entity` AS ce ON pas.customer_id = ce.entity_id
LEFT JOIN  `customer_entity_varchar` AS cev ON pas.customer_id = cev.entity_id
WHERE cev.attribute_id =5
GROUP BY pas.alert_stock_id
1
SELECT entity_id, group_concat(VALUE SEPARATOR ' ') AS fullname 
FROM customer_entity_varchar AS val
INNER JOIN eav_attribute AS attr ON attr.attribute_id  = val.attribute_id
WHERE attr.attribute_code IN ( 'firstname',  'lastname' ) 
GROUP BY entity_id
0

You can also do it using a join like this:

SELECT first.entity_id,CONCAT(first.value,' ',last.value) FROM `customer_entity_varchar` first 
INNER JOIN customer_entity_varchar last ON first.entity_id = last.entity_id AND last.attribute_id = 7 
WHERE first.attribute_id = 5

In this case, attribute_id 5 is first name and attribute_id 7 is last name.

2
  • -1 attribute_ids can vary
    – Alex
    Commented Feb 5, 2013 at 21:18
  • @Alex Obviously they can. That's why I pointed out the ids in my case. Given the details in the question it seemed the OP is able to put another join in to get the correct ids from the eav_attribute table.
    – mpaepper
    Commented Feb 5, 2013 at 22:22
0

It seems the trick is to use the MYSQL function group_concat:

SELECT entity_id, group_concat(VALUE SEPARATOR ' ') AS fullname 
FROM `customer_address_entity_varchar` 
WHERE attribute_id IN (20,22) 
GROUP BY entity_id

Attribute IDs 20 and 22 represent the first and last name. I've also included the entity_id in order to join this data with other tables.

Edit: Since attribute IDs can vary, this isn't a general solution for all installs.

1
  • -1 attribute_ids can vary
    – Alex
    Commented Feb 5, 2013 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.