Symphony.

Is it possible to use multiple ID's within the "where" statement? Like so?

$myquery->where('system:id', '14,26,46,49,56,85');

It only seems to pick up the first one, but normal datasources would normally work where you can filter by multiple ID's so I thought this might be possible?

If I remember correctly it's not possible. But you can add multiple 'where's, one for each id.

From what I recall, it is possible if you use an array instead of a string.

Not really sure, though..

Right now system:id supports just one value:

https://github.com/nickdunn/symql/blob/master/lib/class.symql.php#L160-163

This is more an oversight than a hard limitation. Ideally it should be able to accept a string or an array and handle the input accordingly, building the $entry_ids array. Please feel free to patch and send a pull request.

Okay, I'll try a few different methods. Thanks.

Not a git user (yet) or I'd submit a pull request (or whatever it's called), but shouldn't line 180 of class.symql.php be changed from $field->buildDSRetrievalSQL(array($filter['value']), $joins, $where, ($filtertype == self::DSFILTER_AND ? TRUE : FALSE));

to

$field->buildDSRetrievalSQL($value, $_joins, $_where, ($filter_type == self::DS_FILTER_AND ? TRUE : FALSE));

TheJester12, apply the change above and using commas should work

Just a quick question about using this in a custom DS. What members of a standard DS class can I get rid of? Or rather, what properties and methods are necessary for the query to run?

I'm guessing construct(), about(), allowEditorToParse(), grab()? Anything else? Do I still need require_once(TOOLKIT . '/class.datasource.php');?

Also (eek) how simple would it be to include sections linked via Subsection Manager to the results, as is currently possible in the standard DS?

If anyone has an example that would be great. The Symphony back-end penny is still pending droppage at the moment!

Ok, looks like all you need in a custom data source is about(), allowEditorToParse() and grab().

And to solve my Subsection Manager based problem, I output an array to the param pool containing the resulting IDs from the query (using SymQL::RETURNRAWCOLUMNS as the result type seemed easiest to iterate through). I was then able to use this for chaining to a standard data source.

That'll do for me. Though one day when I get the chance I will take to time to dissect some extensions and get a better idea of how to write more powerful custom code.

Is it possible to make a 'negative where', something like this:

Title       Type
Article 1   News
Article 2   Message
Article 3   Notification
...

I want to fetch all articles, except those which have the 'Message' type.

There's something like this:

$query->where("type", "!= 'Message'");

Thanks!

SymQL uses the same syntax as a normal data source, so check the docs for the specific field you're filtering to see whether it supports negation.

Okay i've tried to find a solution but didn't get that far...

When trying to query multiple id's of a selectboxlink field I get "Not unique table/alias: 't31'" there seems to be a problem with the 'LEFT JOIN' blocks

this is the SQL being generated with error

SELECT SQL_CACHE `e`.id, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date` FROM `sym_entries` AS `e` LEFT JOIN `sym_entries_data_31` AS `t31` ON (`e`.`id` = `t31`.entry_id) LEFT JOIN `sym_entries_data_31` AS `t31` ON (`e`.`id` = `t31`.entry_id) LEFT JOIN `sym_entries_data_31` AS `t31` ON (`e`.`id` = `t31`.entry_id) WHERE 1 AND `e`.`section_id` = '7' AND `t31`.relation_id IN ('119') OR `t31`.relation_id IN ('117')OR `t31`.relation_id IN ('111') ORDER BY `e`.`creation_date` DESC LIMIT 0, 50;

and this is what it should look like

SELECT SQL_CACHE `e`.id, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date` FROM `sym_entries` AS `e` LEFT JOIN `sym_entries_data_31` AS `t31` ON (`e`.`id` = `t31`.entry_id) WHERE 1 AND `e`.`section_id` = '7' AND `t31`.relation_id IN ('119') OR `t31`.relation_id IN ('117')OR `t31`.relation_id IN ('111') ORDER BY `e`.`creation_date` DESC LIMIT 0, 50;

would be really happy about hints and tips!

query sample:

$query
            ->select('system:id')
            ->from('section')
            ->orderby('system:date', 'desc')
            ->perPage(50)
            ->page(1);

        $query->where('some_select_box_link_field', 124);
        $query->where('some_select_box_link_field', 123, SymQL::DS_FILTER_OR);

Does DS_FILTER_AND solve the issue?

same error

sql:

SELECT SQL_CACHE `e`.id, `e`.section_id, e.`author_id`, UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date` FROM `sym_entries` AS `e` LEFT JOIN `sym_entries_data_31` AS `t31` ON (`e`.`id` = `t31`.entry_id) LEFT JOIN `sym_entries_data_31` AS `t31` ON (`e`.`id` = `t31`.entry_id) WHERE 1 AND `e`.`section_id` = '7' AND `t31`.relation_id IN ('124') AND `t31`.relation_id IN ('123') ORDER BY `e`.`creation_date` DESC LIMIT 0, 50

For every where it adds a AS t31 and I guess this is the problem...

See Multiple where conditions giving SQL error. I think I ended up building the query myself.

hmm yeah, doesn't seem to work... any pointers on how to build the query on your own?

thanks!

got it! I changed the symql.php to not parse if an array is passed:

            if(is_string($filter['value'])) {
            $filter_type = (FALSE === strpos($filter['value'], '+') ? self::DS_FILTER_OR : self::DS_FILTER_AND);
            $value = preg_split('/'.($filter_type == self::DS_FILTER_AND ? '+' : ',').'s*/', $filter['value'], -1, PREG_SPLIT_NO_EMPTY);
        } else {
            $value = $filter['value'];
        }
        $value = array_map('trim', $value);

        // Get the WHERE and JOIN from the field
        $where_before = $_where;
        if(is_string($filter['value'])) {
            $field->buildDSRetrivalSQL(array($filter['value']), $_joins, $_where, ($filter_type == self::DS_FILTER_AND ? TRUE : FALSE));
        } else {
            $field->buildDSRetrivalSQL($filter['value'], $_joins, $_where, ($filter_type == self::DS_FILTER_AND ? TRUE : FALSE));
        }

Any clues on how to filter a simple date field and check if it is empty?

something like:

->where('date', "NULL");

Thanks!

Please check the documentation for the Date field. I don't think it supports null/negation. Perhaps you could use a regex, if it supports that. SymQL where syntax is identical to a normal Symphony data source filter, so if it doesn't work there, it doesn't work in SymQL.