How to compare two literals in ActiveRecord, e.g., 1 > 2

Yii 2.0 General Discussions
1

I need to be able to use an ActiveRecord Query class to refind my search to “is the day of week X AND other conditions.” I am stumped and having issues figuring this out, esp since I can do it easily in mysqlc but totally fail when doing the same where clause in ActiveRecord.

I have an ActiveRecord that is defined like this:

type = enum('recurring','once')
recurring = enum('every day', 'every week day')
dow=integer()
...

If type=‘recurring’, then I want to know if it is ‘every day’ or ‘every week day’. For ‘every day’, that’s easy because I don’t have to compare the current day to anything:

[ 'and',
	[ 'rtm_schedule_type.name'           => ScheduleType::SCHEDULE_TYPE_RECURRING],
	[ 'rtm_schedule_option.recurring'    => ScheduleOption::SCHEDULE_OPTION_RECURRING_EVERY_DAY],
],

Where I’m struggling is with ‘every week day’. In mysqlc, I can do this:

mysql> select count(id) as cntid from rtm_category where 3 < 5;
+-------+
| cntid |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)

Put another way, I can say the following to determine if I am within ‘every week day’:

select * from table where `dow +%w` >= 1 and  `dow +%w` <= 5

However, I can’t make this work with ActiveRecord. I’ve tried everything, including variations on the format below, parameters, passing a single string with SQL, etc:

[ 'and',
	[ 'rtm_schedule_type.name'           => ScheduleType::SCHEDULE_TYPE_RECURRING],
	[ 'rtm_schedule_option.recurring'    => ScheduleOption::SCHEDULE_OPTION_RECURRING_EVERY_WEEK_DAY],
	[ '>=', $dateToUse->format('w'), '1' ],
],

Generally I get an error like this:

Caused by: Exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column '4' in 'where clause''

And the error is here:

((`rtm_schedule_type`.`name`='Recurring') AND (`rtm_schedule_option`.`recurring`='Every Week Day') AND (`4` >= '1'))

Specifically, here: (4 >= ‘1’)

I am trying to extend my Query class and am really stumped on this one. It’s probably obvious, but I can’t figure it out after reading the docs a few times: Working with Databases: Active Record | The Definitive Guide to Yii 2.0 | Yii PHP Framework

Maybe I’m approaching this in the wrong way? Or, I just don’t know the syntax that AR wants for this…

2

Figured it out! Leaving here for others. Pretty silly really. If I’m focused on figuring out the day of week, I just need to use the MySQL function for that in my code. Here we go:

[ 'and',
	[ 'rtm_schedule_type.name'           => ScheduleType::SCHEDULE_TYPE_RECURRING],
	[ 'rtm_schedule_option.recurring'    => ScheduleOption::SCHEDULE_OPTION_RECURRING_EVERY_WEEK_DAY],
	[ 'between', 'WEEKDAY(curdate())', 1, 5 ],
],
3

I am glad you found a way, but fundamentally (while SQL allows it) comparing two constants inside a SQL query should be avoided and done outside of the query. You will end up with an if condition rather than a SQL where clause.

4

I don’t agree that this is a bad approach. Can you help me understand why you consider it bad?

If I convert this into an if() outside of the SQL query, my SQL will have to return a larger result that I have to process and refine OR I have to issue multiple queries to the SQL engine. Using this approach, I can issue a single query that gives me the exact results I need.

I am open to criticism, but I’m not seeing the issue you are mentioning.

5

My thought process here is that, if you already have the two literals to compare, you can avoid the SQL itself. It is quite possible I don’t fully understand what you are trying to do. And it’s an opinion, and in the scheme of things I don’t think it makes any difference at all. It seemed to me you can avoid a SQL call by using a condition outside.

6

@dp778899
I think you could use yii\db\Expression to compare two literals.

[ 'and',
	[ 'rtm_schedule_type.name'           => ScheduleType::SCHEDULE_TYPE_RECURRING],
	[ 'rtm_schedule_option.recurring'    => ScheduleOption::SCHEDULE_OPTION_RECURRING_EVERY_WEEK_DAY],
	[ '>=', new Expression($dateToUse->format('w')), 1 ],
],

or

[ 'and',
	[ 'rtm_schedule_type.name'           => ScheduleType::SCHEDULE_TYPE_RECURRING],
	[ 'rtm_schedule_option.recurring'    => ScheduleOption::SCHEDULE_OPTION_RECURRING_EVERY_WEEK_DAY],
	new Expression($dateToUse->format('w') . ' >= 1'),
],

Sorry, but they are not tested.

@snathan
A literal in a SQL statement could be a variable in a PHP program. As you see $dateToUse->format('w') in the code snippet of @dp778899, you may want to use a PHP variable as a literal in a SQL statement. So if you want to avoid comparing literals in SQL, you may end up writing more PHP source lines with if/else or swich/case and many SQLs that are slightly different.

1 Like
7

I came across a similar situation myself. Resolved it by conditionally adding a where clause to the query. Below is the example:

//Define base query
$qAtable = Sometable::find()->andWhere(['somecolumn' => 'N'])
     ->andWhere(['anothercolumn' => $anothervariable])
     ->andWhere(['<=','onemorecolumn', $onemorevar]);

// Add SQL Where condition, if PHP condition is met, to the Query
if ($specificvar <= 700)
    $qAtable->andWhere(['not', ['colstatus' => 'Started']]);

// Execute
$dpAtable = new ActiveDataProvider(['query' => $qAtable,]);
1 Like