Untitled

# Simple: search with 1-to-1 and 1-to-n sub-objects
SELECT
  t1.category_id,
  t1.date_created,
  t1.fk1,
  t1.fk2,
  t1.fk3,
  t1.id,
  t1.last_modified,
  t1.name,
  t1.published,
  t1.status,
  t2.id,
  t2.product_id,
  t2.name,
  t3.id,
  t3.name
FROM
  (
    SELECT DISTINCT t1.category_id, t1.date_created, t1.fk1, t1.fk2, t1.fk3, t1.id, t1.last_modified, t1.name, t1.published, t1.status FROM
      rose_db_object_test_products t1
      JOIN rose_db_object_test_categories t3 ON (t1.category_id = t3.id)
      LEFT OUTER JOIN rose_db_object_test_code_names t2 ON (t1.id = t2.product_id)
    WHERE
      t1.id <= ? AND
      t1.id >= ? AND
      t1.name LIKE ?
    ORDER BY t1.id
    LIMIT 1000
  ) t1
  JOIN rose_db_object_test_categories t3 ON (t1.category_id = t3.id)
  LEFT OUTER JOIN rose_db_object_test_code_names t2 ON (t1.id = t2.product_id)
WHERE
  t1.id <= ? AND
  t1.id >= ? AND
  t1.name LIKE ?
ORDER BY t1.id (100300, 100000, Product %2%, 100300, 100000, Product %2%)