Is there an alternative to LEFT JOIN in custom tables with WordPress?

2 Views

I have two custom tables in the WordPress database. 

table1:  
id | title | etc  

table2:  
id | table1_id | title | source | etc

I have this left join working perfectly:

Read More
select p.title as ptitle, e.id, e.title, e.source, e.etc
FROM table2 AS e
LEFT JOIN table1 AS p 
ON p.id = e.table1_id 
where e.table1_id = 1

which returns this: 

ptitle1 | id1 |title1 | source1 | etc1
ptitle1 | id2 |title2 | source2 | etc2
ptitle1 | id3 |title3 | source3 | etc3

Is there a way of omitting so many repetitions of ptitle? Something like this: 

ptitle1 | { (id1 |title1 | source1 | etc1), (id2 |title2 | source2 | etc2), (id3...) }
ptitle2 | { null }
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. UPDATED v.2

    I’ve changed the query to reflect change in LEFT JOIN order and also to show object { ptitle, array( null ) } situation – data you needed is in the column new_col:

    select 
    p.title as ptitle, 
    CONCAT('object { ', p.title, ', array( ', IFNULL(GROUP_CONCAT(CONCAT('array(', e.id, ' | ', e.title, ' | ', e.source, ' | ', e.etc, ')') SEPARATOR ', '), 'null'), ' ) }') AS new_col
FROM 
    table1 AS p
    LEFT JOIN table2 AS e  
        ON p.id = e.table1_id 
where 
    p.id = 1
GROUP BY 
    p.title

    Reffering to your comment, new_col is a string and I thought that you need it in this form because you will apply a parser on this text to interprete it and return proper object for you.
    For this case I think you would have to describe the situation more closely, for what exact reason you need this specific form of result with maybe some kind of exaample usage in wordpress api – I’m not sure of that for 100%.