在MYSQL中，是否可以选择将在WHERE子句中定义的内容？

Question

I have a system which contains items that may each belong to more than one parent. I need to be able to retrieve all of an item's children, and it's children's children.. ad nauseam. OK well there are 5 levels.

Each item has (or should have) a json column named "belongs_to_json" (also more columns such as "id" and whatnot) which contains each parent, order combination. e.g. [{"parent": 2, "order": 4},{"parent": 13, "order": 1}]

I can search for this and others with the same parent by combining PHP and MYSQL (idea found on stackoverflow previously)

First I run a query (codeigniter framework) getting some item parents and put the query in a $qry_classes variable.

    $sql_classes = "SELECT t.id 
    FROM table t 
    WHERE JSON_SEARCH(t.belongs_to_json, 'all', '2') LIKE '%.parent%';";
    $qry_classes = $this->db->query($sql_classes);

Then I use the results from that query to write a new WHERE statement.

    $s_rewrite_array = array_map(function($row) {
    return "JSON_SEARCH(t.belongs_to_json, 'all', '{$row->id}') LIKE '%.parent%'";
    }, $qry_classes->return_array() );
    $sections_condition = implode(" OR ", $s_rewrite_array);
    $sql_sections = "
        SELECT  t.id
        FROM    table t
        WHERE   ($sections_condition)
    ";
    $qry_sections = $this->db->query($sql_sections);

The problem comes when I have an item with multiple parents and I want to return the parent that was used in the WHERE clause in the SELECT clause.

Something like

    SELECT t.*, non_existant_column FROM table t WHERE 
    JSON_SEARCH(t.belongs_to_json, 'all', '{$row->id}' as non_existant_column) LIKE '%.parent%;

obviously not a real thing but I'm hoping there is an equivalent. Or maybe different way to solve the issue.

Answer 1

You can just use put a literal in the SELECT list, you don't need to get it from the WHERE clause.

SELECT t.*, '{$row->id}' AS parent 
FROM table t 
WHERE JSON_SEARCH(t.belongs_to_json, 'all', '{$row->id}') LIKE '%.parent%;

If you're using MySQL 8.0, you can probably use JSON_TABLE to find parent: 2 instead of your method. See MySQL Return JSON array index based on property value