using the &where clause

128 Posts

drwagner13 Reply #1, 17 years, 2 months ago

I am trying to select all documents whose pagetitle begins with a range of letters, such as [A-F].

Using the Ditto &where clause, I have successfully been able to retrieve documents beginning with any single letter using:

&where=`pagetitle LIKE ’A%’`

However, the following does not work:

&where=`pagetitle LIKE ’[A-F]%’`

I have tried to simply the problem by retrieving documents beginning with A using the following:

&where=`pagetitle LIKE ’[A]%’`

but this generates the same "ModX parse error" screen as the one above.

I thought perhaps the brackets were causing the problem, so I attempted the following:

&where=`pagetitle LIKE ’[F]%’` //escaping the brackets with html entities

AND

&where=`pagetitle LIKE ’/[F/]%’` //escaping with slashes

AND

&where=`pagetitle LIKE ’[+[F]%:esc+]’` //escaping using phx

But nothing works.

Does anyone know how to use the where clause with regular expressions to get the desired result?

(Incidentally, it is interesting that the &where clause documentation states that you should supply a "valid MySQL WHERE statement" but does not state the the word WHERE should not be included in the clause itself!)

Revo 2.0.8-pl

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 17 years, 2 months ago

I believe you’ll need to use REGEXP instead of LIKE: http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

2,314 Posts

anonymized-26931 Reply #3, 17 years, 2 months ago

Hi

maybe this thread helps with what you try to accomplish: http://modxcms.com/forums/index.php/topic,36667.0.html

128 Posts

drwagner13 Reply #4, 17 years, 2 months ago

Thanks sharkbait, but I’m trying to use the where clause in ditto to accomplish this, and that should work, in principle.

sottwell: I tried REGEXP, but doubt it’s necessary, and it didn’t work anyway, even in the simplified form:

&where=`pagetitle REGEXP ’F%’`

You can use regular expressions in WHERE clauses in SQL. It is that the brackets in the clause that seem to be causing the problem. I’ve copied the error page message here, in which you can see that the entire part of the clause following LIKE or REGEXP is dropped:

« MODx Parse Error »
MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND (sc.privateweb=0 OR dg.document_group IN (3)) GROUP BY sc.id ORDER BY sc.pag' at line 4 »
      SQL: SELECT DISTINCT sc.id,sc.published,sc.pagetitle FROM `cssa_shop`.`modx_site_content` sc LEFT JOIN `cssa_shop`.`modx_document_groups` dg on dg.document = sc.id WHERE sc.id IN (449,505,548,616,670,717,767,807,1480,3455,3456,3457,3458,3459,3460,3461,3462,3463,3464,3465,3466,3467,3468,3469,3470,3471,3472,3473,3474,3475,3476,3477,3478,3479,3480,3481,3482,3483,3484,3485,3486,3487,3488,3489,3490,3491,3492,3493,3494,3495,3496,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3507,3508,3509,3510,3511,3512,3513,3514,3515,3516,3517,3518,3519,3520,3521,3522,3523,3524,3525,3526,3527,3528,3529,3530,3531,3532,3533,3534,3535,3536,3537,3538,3539,3540,3541,3542,3543,3544,3545,3546,3547,3548,3549,3550,3551,3552,3553,3554,3555,3556,3557,3558,3559,3560,3561,3562,3563,3564,3565,3566,3567,3568,3569,3570,3571,3572,3573,3574,3575,3576,3577,3578,3579,3580,3581,3582,3583,3584,3585,3586,3587,3588,3589,3590,3591,3592,3593,3594,3595,3596,3597,3598,3599,3600,3601,3602,3603,3604,3605,3606,3607,3608,3609,3610,3611,3612,3613,3614,3615,3616,3617,3618,3619,3620,3621,3622,3623,3624,3625,3626,3627,3628,3629,3630,3631,3632,3633,3634,3635,3636,3637,3638,3639,3640,3641,3642,3643,3644,3645,3646,3647,3648,3649,3650,3651,3652,3653,3654,3655,3656,3657,3658,3659,3660,3661,3662,3663,3664,3665,3666,3667,3668,3669,3670,3671,3672,3673,3674,3675,3676,3677,3678,3679,3680,3681,3682,3683,3684,3685,3686,3687,3688,3689,3690,3691,3692,3693,3694,3695,3696,3697,3698,3699,3700,3701,3702,3703,3704,3705,3706,3707,3708,3709,3710,3711,3712,3713,3714,3715,3716,3717,3718,3719,3720,3721,3722,3723,3724,3725,3726,3727,3728,3729,3730,3731,3732,3733,3734,3735,3736,3737,3738,4035,4036,4037,4038,4039,4040,4041,4042,4043,4044,4045,4046,4047,4048,4049,4050,4051,4052,4053,4054,4055,4056,4057,4058,4059,4060,4061,4062,4063,4064,4065,4066,4067,4068,4069,4070,4071,4072,4073,4074,4075,4076,4077,4078,4079,4080,4081,4082,4083,4084,4085,4086,4087,4088,4089,4090,4091,4092,4093,4094,4095,4096,4097,4098,4099,4100,4101,4102,4103,4104,4105,4106,4107,4108,4109,4110,4111,4112,4113,4114,4115,4116,4117,4118,4119,4120,4121,4122,4123,4124,4125,4126,4127,4128,4129,4130,4131,4132,4133,4134,4135,4136,4137,4138,4139,4140,4141,4142,4143,4144,4145,4146,4147,4148,4149,4150,4151,4152,4153,4154,4155,4156,4157,4158,4159,4160,4161,4162,4163,4164,4165,4166,4167,4168,4169,4170,4171,4172,4173,4174,4175,4176,4177,4178,4179,4180,4181,4182,4183,4184,4185,4186,4187,4188,4189,4190,4191,4192,4193,4194,4195,4196,4197,4198,4199,4200,4201,4202,4203,4204,4205,4206,4207,4208,4209,4210,4211,4212,4213,4214,4215,4216,4217,4218,4219,4220,4221,4222,4223,4224,4225,4226,4227,4228,4229,4230,4231,4232,4233,4234,4235,4236,4237,4238,4239,4240,4241,4242,4243,4244,4245,4246,4247,4248,4249,4250,4251,4252,4253,4254,4255,4256,4257,4258,4259,4260,4261,4262,4263,4264,4265,4266,4267,4268,4269,4270,4271,4272,4273,4274,4275,4276,4277,4278,4279,4280,4281,4282,4283,4284,4285,4286,4287,4288,4289,4290,4291,4292,4293,4294,4295,4296,4297,4298,4299,4300,4301,4302,4303,4304,4305,4306,4307,4308,4309,4310,4311,4312,4313,4314,4315,4316,4317,4318,4390,4391,4392,4393,4394,4395,4396,4397,4398,4399,4400,4401,4402,4403,4404,4405,4406,4407,4408,4409,4410,4411,4412,4413,4414,4644,4645,4646,4647,4648,4649,4650,4651,4652,4653,4654,4655,4656,4657,4658,4659,4660,4661,4662,4663,4664,4665,4666,4667,4873,4874,4875,4876,4877,4878,4879,4880,4881,4882,4883,4884,4885,4886,4887,4888,4889,4890,4891,4892,4893,4894,4895,4896,4897,4898,4899,4900) AND sc.published=1 AND sc.deleted=0 AND sc.isfolder = 0 AND sc.pagetitle REGEXP AND (sc.privateweb=0 OR dg.document_group IN (3)) GROUP BY sc.id ORDER BY sc.pagetitle
      [Copy SQL to ClipBoard]
 
Parser timing
  MySQL: 	0.0332 s	(33 Requests)
  PHP: 	0.8140 s	 
  Total:

« MODx Parse Error »
MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND (sc.privateweb=0 OR dg.document_group IN (3)) GROUP BY sc.id ORDER BY sc.pag' at line 4 »
      SQL: SELECT DISTINCT sc.id,sc.published,sc.pagetitle FROM `cssa_shop`.`modx_site_content` sc LEFT JOIN `cssa_shop`.`modx_document_groups` dg on dg.document = sc.id WHERE sc.id IN (449,505,548,616,670,717,767,807,1480,3455,3456,3457,3458,3459,3460,3461,3462,3463,3464,3465,3466,3467,3468,3469,3470,3471,3472,3473,3474,3475,3476,3477,3478,3479,3480,3481,3482,3483,3484,3485,3486,3487,3488,3489,3490,3491,3492,3493,3494,3495,3496,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3507,3508,3509,3510,3511,3512,3513,3514,3515,3516,3517,3518,3519,3520,3521,3522,3523,3524,3525,3526,3527,3528,3529,3530,3531,3532,3533,3534,3535,3536,3537,3538,3539,3540,3541,3542,3543,3544,3545,3546,3547,3548,3549,3550,3551,3552,3553,3554,3555,3556,3557,3558,3559,3560,3561,3562,3563,3564,3565,3566,3567,3568,3569,3570,3571,3572,3573,3574,3575,3576,3577,3578,3579,3580,3581,3582,3583,3584,3585,3586,3587,3588,3589,3590,3591,3592,3593,3594,3595,3596,3597,3598,3599,3600,3601,3602,3603,3604,3605,3606,3607,3608,3609,3610,3611,3612,3613,3614,3615,3616,3617,3618,3619,3620,3621,3622,3623,3624,3625,3626,3627,3628,3629,3630,3631,3632,3633,3634,3635,3636,3637,3638,3639,3640,3641,3642,3643,3644,3645,3646,3647,3648,3649,3650,3651,3652,3653,3654,3655,3656,3657,3658,3659,3660,3661,3662,3663,3664,3665,3666,3667,3668,3669,3670,3671,3672,3673,3674,3675,3676,3677,3678,3679,3680,3681,3682,3683,3684,3685,3686,3687,3688,3689,3690,3691,3692,3693,3694,3695,3696,3697,3698,3699,3700,3701,3702,3703,3704,3705,3706,3707,3708,3709,3710,3711,3712,3713,3714,3715,3716,3717,3718,3719,3720,3721,3722,3723,3724,3725,3726,3727,3728,3729,3730,3731,3732,3733,3734,3735,3736,3737,3738,4035,4036,4037,4038,4039,4040,4041,4042,4043,4044,4045,4046,4047,4048,4049,4050,4051,4052,4053,4054,4055,4056,4057,4058,4059,4060,4061,4062,4063,4064,4065,4066,4067,4068,4069,4070,4071,4072,4073,4074,4075,4076,4077,4078,4079,4080,4081,4082,4083,4084,4085,4086,4087,4088,4089,4090,4091,4092,4093,4094,4095,4096,4097,4098,4099,4100,4101,4102,4103,4104,4105,4106,4107,4108,4109,4110,4111,4112,4113,4114,4115,4116,4117,4118,4119,4120,4121,4122,4123,4124,4125,4126,4127,4128,4129,4130,4131,4132,4133,4134,4135,4136,4137,4138,4139,4140,4141,4142,4143,4144,4145,4146,4147,4148,4149,4150,4151,4152,4153,4154,4155,4156,4157,4158,4159,4160,4161,4162,4163,4164,4165,4166,4167,4168,4169,4170,4171,4172,4173,4174,4175,4176,4177,4178,4179,4180,4181,4182,4183,4184,4185,4186,4187,4188,4189,4190,4191,4192,4193,4194,4195,4196,4197,4198,4199,4200,4201,4202,4203,4204,4205,4206,4207,4208,4209,4210,4211,4212,4213,4214,4215,4216,4217,4218,4219,4220,4221,4222,4223,4224,4225,4226,4227,4228,4229,4230,4231,4232,4233,4234,4235,4236,4237,4238,4239,4240,4241,4242,4243,4244,4245,4246,4247,4248,4249,4250,4251,4252,4253,4254,4255,4256,4257,4258,4259,4260,4261,4262,4263,4264,4265,4266,4267,4268,4269,4270,4271,4272,4273,4274,4275,4276,4277,4278,4279,4280,4281,4282,4283,4284,4285,4286,4287,4288,4289,4290,4291,4292,4293,4294,4295,4296,4297,4298,4299,4300,4301,4302,4303,4304,4305,4306,4307,4308,4309,4310,4311,4312,4313,4314,4315,4316,4317,4318,4390,4391,4392,4393,4394,4395,4396,4397,4398,4399,4400,4401,4402,4403,4404,4405,4406,4407,4408,4409,4410,4411,4412,4413,4414,4644,4645,4646,4647,4648,4649,4650,4651,4652,4653,4654,4655,4656,4657,4658,4659,4660,4661,4662,4663,4664,4665,4666,4667,4873,4874,4875,4876,4877,4878,4879,4880,4881,4882,4883,4884,4885,4886,4887,4888,4889,4890,4891,4892,4893,4894,4895,4896,4897,4898,4899,4900) AND sc.published=1 AND sc.deleted=0 AND sc.isfolder = 0 AND sc.pagetitle LIKE AND (sc.privateweb=0 OR dg.document_group IN (3)) GROUP BY sc.id ORDER BY sc.pagetitle
      [Copy SQL to ClipBoard]
 
Parser timing
  MySQL: 	0.0329 s	(33 Requests)
  PHP: 	0.8497 s	 
  Total: 	0.8826 s

Revo 2.0.8-pl

128 Posts

drwagner13 Reply #5, 17 years, 2 months ago

Continuing down this path, I’ve discovered that the following do not work:

&where=`pagetitle < ’M’` (returns nothing)
&where=`pagetitle BETWEEN ’M’ AND ’Z’` (generates and error due to sql statment looking like this: ...AND sc.pagetitle BETWEEN ’M’ AND sc.’Z’... )

Has anyone gotten the where clause to work with Ditto with these sorts of expessions? I know I have the syntax at least partially correct, since the following works:

&where=`pagetitle LIKE ’F%’`

but the following do not:

&where=`pagetitle LIKE ’[F]%’`
&where=`pagetitle LIKE ’[F-J]%’`

Revo 2.0.8-pl

128 Posts

drwagner13 Reply #6, 17 years, 2 months ago

Well, the &where clause is a bust, but I found a workaround using the &filter clause (abysmally documented, so I’ll explain here):

If you want to select documents whose pagetitle falls within an alphabetical range, you can use the &filter classe in ditto. For example, lets select all documents whose pagetitel begins with B through G thusly:

&filter=`pagetitle,B,3|pagetitle,H,4`

Break it down

"pagetitle,B,3" does the following:

1. filters on the pagetitle
2. B is the filter critereon
2. 3 is the clause’s "less than" mode

so this discards all pagetitles that are alphabetically less than the letter B.

The pipe symbol "|" allows us to other criteria, so we’ve used mode "4" in the second clause, the "greater than criteria, to discard page titles alphabetically above H (note that if you were to use "G", then all documents that begin with G, but not equal to "G" would be filtered out. However documents whose title ="H" will be included in the data set). Using the filter mode "6" (greater than or equal to) produced unexpected results in my tests. However, filter mode 5 (less than or equal to) in the second clause gave the correct result (not returning a content item whose pagetitle = "H". Is this indicative of a mistake in the code or the documentation?

Also, note that I did not use single quotes around the criteria in these filter clauses. This does not work, although some online documentation indicates otherwise. Quoting strings in the filter results in the filter being ignored and all documents will be returned, unfiltered.

Some questions remain in my mind with regard to the &filter clause. First, do the filters operate sequentially (does order matter). Could I select a non-contiguous alphabetical range this way (B-G, M-P)?

More testing

I decided to test the filter mode one at a time to see what I could figure out:

&filter=`pagetitle,H,4`

This returns the expected result, all items than begin with H or higher, and includes the document whose name is "H".

&filter=`pagetitle,H,4`

This returns the expected result, all items than begin with H or lower, and includes the document whose name is "H".

&filter=`pagetitle,H,5`

Does not return the expected result. It returns all documents whose pagetitle is below H and does not exclude H, and this is acting as the greater than or equal to mode.

&filter=`pagetitle,H,6`

Similarly opposite the documentation. Mode 6 is the "less than or equal to" filter mode.

Modes 7 and 8 seems to work as expected, and in some online sources mode 11 is said to do something with regard to the first letter of the filed, and in this case:

&filter=`pagetitle,H,11`

All documents that begin with H are returned.

Inspiration found here:
http://modxcms.com/forums/index.php/topic,36133.0.html

Revo 2.0.8-pl