选择sql多对多关系到嵌套的php对象中

Question

problem

I have two data tables SEQUENCES and ORGANISMS whose many-to-many-relationship is mappend in the table SOURCES. There is also a 1-m relationshipt between SOURCES and ENTRIES. I will append a detailed structure.

What i want to achieve, is the display of all sequences with all associated organisms and entries, where a condition within the sequences table is met. I have some ideas on how to achieve this, but i need the solution with the best performance, as each of these contains 50k+ entries.

idea one

Select all organisms that belong to the same sequence as a concatenated string in sql, and split it in PHP. I have no idea though, how to do the concatenation in SQL.

idea two

select same sequences with different organisms as distinct records, order by organism, and join them later in php. though this somehow feels just wrong.

idea three

use views. ANY idea on this one appreciated

structure

SEQUENCES
  SEQUENCE_ID
  DESCRIPTION

ORGANISMS
  ORGANISM_ID
  NAME

SOURCES
  SOURCE_ID
  SEQUENCE_ID FK to SEQUENCES.SEQUENCE_ID
  ORGANISM_ID FK to ORGANISMS.ORGANISM_ID

ENTRIES
  SOURCE_ID FK to SOURCES.SOURCE_ID
  ENTRY_VALUE

desired outcome

array(
  array(
    "SEQUENCE_ID" => 4,
    "DESCRIPTION" => "Some sequence",
    "SOURCES" => array(
      array(
        "ORGANISM_ID" => 562,
        "ORGANISM_NAME" => "Escherichia coli",
        "ENTRIES" => array(
          "some entry",
          "some other entry"
        ),
      array(
        "ORGANISM_ID" => 402764,
        "ORGANISM_NAME" => "Aranicola sp. EP18",
        "ENTRIES" => array()
      )
    )
  ),
  array(
    "SEQUENCE_ID" => 5,
    .....
  )
)

PHP5 and FIREBIRD2.5.1

Answer 1

You can't fetch a nested array like that directly from a flat table structure. But if I get you right, what you want to do is not that hard to achieve.

I don't understand why you would concatenate things and then split them again, that's hard to maintain and probably slow.

I see two approaches here:

1. Fetch everything at once as flat table using JOIN and loop through it in PHP. This approach creates a lot of duplication but it's fast because you can fetch all data in one query and then process it with PHP.

2. Fetch every entity separately, loop and fetch the next hierarchy level as you go. This approach will be slower. It takes complexity away from the SQL query and doesn't fetch redunant data. It also gives you more freedom as to how you loop through your data and what you do with it.

Alternatively you might want to actually store hierarchical data in a no-sql way, where you could already store the array structure you mentioned.