Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- entities
- id name
- 1 Apple
- 2 Orange
- 3 Banana
- 4 Carrot
- 5 Mushroom
- entity_entity
- entity_id_a entity_id_b
- 1 2
- 5 1
- 4 1
- 5 4
- 1 3
- CREATE UNIQUE INDEX ix_a_b ON entity_entity(entity_id_a, entity_id_b);
- CREATE INDEX ix_b ON entity_entity(entity_id_b);
- INSERT
- INTO entity_entity (entity_id_a, entity_id_b)
- VALUES (LEAST(@id1, @id2), GREATEST(@id1, @id2))
- SELECT entity_id_b
- FROM entity_entity
- WHERE entity_id_a = @id
- UNION ALL
- SELECT entity_id_a
- FROM entity_entity
- WHERE entity_id_b = @id
- SELECT related.* FROM entities AS search
- LEFT JOIN entity_entity map ON map.entity_id_a = search.id
- LEFT JOIN entities AS related ON map.entity_id_b = related.id
- WHERE search.name = 'Search term'
- CREATE TABLE equivalence_class (
- class_id int -- surrogate, IDENTITY, autonumber, etc.
- ,entity_id int
- )
- SELECT c2.entity_id
- FROM equivalence_class c1
- INNER JOIN equivalence_class c2
- ON c1.entity_id = @entity_id
- AND c1.class_id = c2.class_id
- AND c2.entity_id <> @entity_id
- SELECT c2.entity_id
- FROM equivalence_class c1
- INNER JOIN equivalence_class c2
- ON c1.entity_id = @entity_id
- AND c1.class_id = c2.class_id
- AND c2.entity_id <> c1.entity_id
- SELECT DISTINCT
- CASE
- WHEN entity_id_a <> @entity_id THEN entity_id_a
- WHEN entity_id_b <> @entity_id THEN entity_id_b
- END AS equivalent_entity
- FROM entity_entity
- WHERE entity_id_a = @entity_id OR entity_id_b = @entity_id
- SELECT entity_id_b AS equivalent_entity
- FROM entity_entity
- WHERE entity_id_a = @entity_id
- UNION
- SELECT entity_id_a AS equivalent_entity
- FROM entity_entity
- WHERE entity_id_b = @entity_id
- select * from entities
- where entity_id in
- (
- select entity_id_b
- from entity_entity
- where entity_id_a = @lookup_value
- )
- select if(entity_id_a=:entity_id,entity_id_b,entity_id_a) as related_entity_id where :entity_id in (entity_id_a, entity_id_b)
Add Comment
Please, Sign In to add comment