MySQL WhereIN subquery limit

Question

Let's say I have this massive query:

SELECT
    entity_documents.id,
    entity_fields.name,
    entity_document_fields.value,
    entity_documents.created_at,
    entity_documents.updated_at

FROM entity_documents

LEFT JOIN entity_document_fields
    ON entity_documents.id = entity_document_fields.entity_document_id

LEFT JOIN entity_fields
    ON entity_document_fields.entity_field_id = entity_fields.id

WHERE entity_documents.id IN (
    SELECT
        entity_document_fields.entity_document_id

    FROM entities

    LEFT JOIN entity_fields
        ON entities.id = entity_fields.entity_id

    LEFT JOIN entity_document_fields
        ON entity_fields.id = entity_document_fields.entity_field_id

    WHERE entities.name = "Pages"
    AND entity_fields.name = "Slug"
    AND entity_document_fields.value = '/'

    LIMIT 10
);

When I run this, MySQL gives the following error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Is there any way around this problem?

I'm using MySQL 5.5.34. Do I need to upgrade or is there a better solution available to limit my subquery?

Thanks in advance

possible duplicate of [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](http://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu), also [this one](http://stackoverflow.com/questions/5004129/problem-with-limit-in-all-any-some-subquery) — Michael Berkowski, Apr 16 '14 at 20:21

score 1 · Answer 1 · answered Apr 16 '14 at 20:29

Move the condition to the from clause:

SELECT
    entity_documents.id,
    entity_fields.name,
    entity_document_fields.value,
    entity_documents.created_at,
    entity_documents.updated_at

FROM entity_documents LEFT JOIN
     entity_document_fields
     ON entity_documents.id = entity_document_fields.entity_document_id LEFT JOIN 
     entity_fields
     ON entity_document_fields.entity_field_id = entity_fields.id JOIN
     (SELECT DISTINCT entity_document_fields.entity_document_id
      FROM entities LEFT JOIN
           entity_fields
           ON entities.id = entity_fields.entity_id LEFT JOIN
           entity_document_fields
           ON entity_fields.id = entity_document_fields.entity_field_id
      WHERE entities.name = 'Pages' AND
            entity_fields.name = 'Slug' AND
            entity_document_fields.value = '/'
      LIMIT 10
     ) filter
     on filter.entity_document_id =  entity_documents.id;

The distinct is not necessary if you know that the values returned by the subquery are already unique.

MySQL WhereIN subquery limit

1 Answers1