DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

A List of Tags inside of a DataTables Editor column

A List of Tags inside of a DataTables Editor column

northcuttnorthcutt Posts: 9Questions: 2Answers: 0
in Editor

Using DataTables Editor with inline editing, and it's working great, all except for one kind of unique thing I need to do.

I need to manage a list of "tags". That means two regular data tables (lw_opportunities, lw_qualifiers) and one associative table between them (lw_qualifier_assoc). Here's the working SQL query:

SELECT lw_opportunities.*,
       GROUP_CONCAT(lw_qualifiers.tag ORDER BY lw_qualifiers.tag) AS tags
FROM lw_opportunities
    LEFT JOIN lw_qualifier_assoc
        ON lw_opportunities.op_id = lw_qualifier_assoc.op_id
    LEFT JOIN lw_qualifiers
        ON lw_qualifier_assoc.q_id = lw_qualifiers.q_id
GROUP BY lw_opportunities.op_id

I don't need inline editing on this row; obviously it's too complex for that, and I've got a good thing going for that outside of DataTables using jQuery UI. I'm just getting a bit hung-up on the best way to tie this extra data into a separate column.

I found an older post that suggests that GROUP BY won't work in the format that the DataTables Editor PHP class requires.

Is there some other way to still return at the time that the main Editor JSON is generated? Or am I left to use some other method of looped, additional AJAX calls? Iterate(), maybe? Obviously that's not ideal, but at this stage, I'm looking for any way to make this work.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 64,767Questions: 1Answers: 10,721 Site admin

    Unfortunately you are correct that the pre-built Editor libraries don't have a GROUP BY option, however, it looks like you simply need a one-to-many join here - is that correct?

    If so the Mjoin class can do that :

    Does that fit your needs?

    Allan

  • northcuttnorthcutt Posts: 9Questions: 2Answers: 0

    It looks like it will! Thanks Allan. I think I'm 90% the way there now; the generated JSON is throwing this error:

    {"sError":"Table selected fields (i.e. '{table}.{column}') in `Join` must be read only. Use `set(false)` for the field to disable writing."}

    For what looks to be the format in the example:

        Editor::inst( $db, 'lw_opportunity_sites','os_id' )
        ->fields(
            Field::inst( 'lw_opportunity_sites.domain' )->validator( 'Validate::notEmpty' ),
            Field::inst( 'lw_opportunity_sites.submit_url' ),
            Field::inst( 'lw_opportunity_sites.section_url' ),
            Field::inst( 'lw_opportunity_sites.notes' ),
            Field::inst( 'lw_opportunity_sites.cost' ),         
            Field::inst( 'lw_opportunity_sites.op_id' )         
        )       
        ->join(
            Mjoin::inst('lw_qualifiers')
                ->link( 'lw_qualifier_assoc.os_id', 'lw_opportunity_sites.os_id' )
                ->link( 'lw_qualifier_assoc.q_id', 'lw_qualifiers.q_id' )
                ->fields(
                    Field::inst( 'lw_qualifiers.q_id' ),
                    Field::inst( 'lw_qualifiers.tag' )          
                )               
        )
        ->where("lw_opportunity_sites.op_id",$op_id)
        ->process( $_POST )
        ->json();

    Not sure how to define that field as read-only? Doesn't appear that the example does anything to. It was also telling me that there's no order() method in the Mjoin class before that, which was kind of weird.

    I popped open Mjoin.php in my copy of Editor, and there are only 33 lines of code there; defining it as a subclass of Join and a simple constructor. Not sure if there should be more.

  • allanallan Posts: 64,767Questions: 1Answers: 10,721 Site admin
    Answer ✓

    Change:

                    Field::inst( 'lw_qualifiers.q_id' ),
                Field::inst( 'lw_qualifiers.tag' )

    to:

                    Field::inst( 'q_id' ),
                Field::inst( 'tag' )

    No need to specify the table name - that is already done with the Mjoin constructor.

    It was also telling me that there's no order() method in the Mjoin class before that, which was kind of weird.

    That's actually correct in the current release - sorry about that! The site is using a slightly newer version of the Editor libraries than then current release.

    I popped open Mjoin.php in my copy of Editor, and there are only 33 lines of code there; defining it as a subclass of Join and a simple constructor. Not sure if there should be more.

    That's correct. Its just an alias of Join for naming consistency with other components. The Join class name is legacy, but for backwards compatibility is retained.

    Allan

  • northcuttnorthcutt Posts: 9Questions: 2Answers: 0

    OH MY GOD IT'S WORKING!

    That did it, thanks again!

This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2025 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.