[SOLVED] a AND ((a OR a) AND (a OR a))

116 Posts

theboxer Reply #1, 15 years, 4 months ago

Any way how to do this pls (revo 2.1.1)?

    $e->where(
        array(
            'idItem' => $scriptProperties['idItem'],
            'openDay' => $scriptProperties['openDay'],
            array(
                array(
                    'openFrom:<=' => $scriptProperties['openFrom'],
                    'openTo:>=' => $scriptProperties['openFrom']
                ),
                array(
                    'OR:openFrom:<=' => $scriptProperties['openTo'],
                    'openTo:>=' => $scriptProperties['openTo']
                )
            )
        )
    );
     
     
    >> THIS MAKES >>
     
    (
            `idItem` = 1 AND `openDay` = 0
           
            OR // need AND here
           
            (
                    ( `openFrom` <= '10:00' AND `openTo` >= '10:00' )
                    OR
                    ( `openFrom` <= '22:00' AND `openTo` >= '22:00' )
            )
    )

--
John
@theboxer

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #2, 15 years, 4 months ago

 $e->where(
        array(
            'idItem' => $scriptProperties['idItem'],
            'openDay' => $scriptProperties['openDay']
   )
);
$e->andCondition(array(
                array(
                    'openFrom:<=' => $scriptProperties['openFrom'],
                    'openTo:>=' => $scriptProperties['openFrom']
                ),
                array(
                    'OR:openFrom:<=' => $scriptProperties['openTo'],
                    'openTo:>=' => $scriptProperties['openTo']
                )
);

Untested, but should point you in the right direction.

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

116 Posts

theboxer Reply #3, 15 years, 4 months ago

tested, didn’t work ->same result

--
John
@theboxer

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #4, 15 years, 4 months ago

If you want a copy/paste solution you can hire me or someone else, but if you can’t even say "thanks" for someone trying to help you out don’t have to expect any "your welcome" either.

Sorry - but that attitude of trying something out in less than one minute and complaining it doesn’t work straight away? That just does not feel right. I spent longer to try and help you out than you did trying it, checking if I made any spelling or syntax mistakes or (God forbid) looking in the documentation for the andCondition method.

This is open source and you get what you give.

EDIT: It seems you edited your post w hile I was working on this oen to say you had the same result as before. Thanks for sharing what the result was.

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

116 Posts

theboxer Reply #5, 15 years, 4 months ago

yea thanks for help, by "tested" I meant I tried this shot before I post my problem here.

EDITED: for my defense, I am tring to solve this more than 2 hours, tried on IRC, now here.

--
John
@theboxer

116 Posts

theboxer Reply #6, 15 years, 4 months ago

Ok, didn’t find solution how to do this by newQuery (so if someone know how to, I will welcome that)

If someone want force solution (I wanted only count of that rows):

$query = "SELECT .... FROM ... WHERE...." //Just query of your dreams;
$stmt = $modx->query($qeury);
$stmt->execute();
$results= $stmt->fetchAll(PDO::FETCH_COLUMN);
$count = count($results);

--
John
@theboxer

☆ A M B ☆
3,141 Posts

Mark Hamstra Reply #7, 15 years, 4 months ago

That doesn’t mean you can just take all help for granted though. There’s nobody on this forum or irc that gets paid to help (minus the core team possibly - though I suspect most of their time on here is in free time as well) so it isn’t fair to demand a solution - and not even show a sign of gratitude for them taking the time to read the forums to help out.

I don’t see a reason why it be using OR there.

Perhaps try splitting it up into more andCondition methods(losing the nested arrays).

$e->where(
        array(
            'idItem' => $scriptProperties['idItem'],
            'openDay' => $scriptProperties['openDay']
   )
);
$e->andCondition(
                array(
                    'openFrom:<=' => $scriptProperties['openFrom'],
                    'openTo:>=' => $scriptProperties['openFrom']
                ));
$e->andCondition(
                array(
                    'OR:openFrom:<=' => $scriptProperties['openTo'],
                    'openTo:>=' => $scriptProperties['openTo']
                ));

Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.

116 Posts

theboxer Reply #8, 15 years, 4 months ago

thx for next shot, tried this before too.

It a little different output, but again wrong

SELECT `BookItemsOpen`.`id` AS `BookItemsOpen_id`, `BookItemsOpen`.`idItem` AS `BookItemsOpen_idItem`, `BookItemsOpen`.`openDay` AS `BookItemsOpen_openDay`, `BookItemsOpen`.`openFrom` AS `BookItemsOpen_openFrom`, `BookItemsOpen`.`openTo` AS `BookItemsOpen_openTo` FROM `modx_book_items_open` AS `BookItemsOpen` WHERE ( ( `BookItemsOpen`.`idItem` = 1 AND `BookItemsOpen`.`openDay` = 0 ) AND ( `BookItemsOpen`.`openFrom` <= '0:00' AND `BookItemsOpen`.`openTo` >= '0:00' ) OR ( `BookItemsOpen`.`openFrom` <= '10:00' AND `BookItemsOpen`.`openTo` >= '10:00' ) )

brackets are wrong...

tried (before) adding group parametr into where, andCOndition methods and the result was same as the first. Good brackets positions, but instead of AND was OR there... like in first post

I know that nobody is paid here for helping others, I am not paid as well for this addon that I am trying to make.

--
John
@theboxer

MODX Staff
10,725 Posts

opengeek Reply #9, 15 years, 4 months ago

This appears to be a bug in the buildConditionalClause() method. Try putting the outer conditions after the nested ones for now and it should work, e.g.

<?php
$e->where(
    array(
        array(
            array(
                'openFrom:<=' => $scriptProperties['openFrom'],
                'openTo:>=' => $scriptProperties['openFrom']
            ),
            array(
                'OR:openFrom:<=' => $scriptProperties['openTo'],
                'openTo:>=' => $scriptProperties['openTo']
            )
        ),
        'idItem' => $scriptProperties['idItem'],
        'openDay' => $scriptProperties['openDay'],
    )
);

This is bug #4877 in the xPDO project.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

116 Posts

theboxer Reply #10, 15 years, 4 months ago

ooo thank a lot, this works, unfortunately I must change the query and add something more, but I solve that too (cause of ur code

)

so now I have:

$e->where(
    array(
        array(
            array(
                array(
                    'openFrom:<=' => $scriptProperties['openFrom'],
                    'openTo:>=' => $scriptProperties['openFrom']
                ),
                array(
                    'OR:openFrom:<=' => $scriptProperties['openTo'],
                    'openTo:>=' => $scriptProperties['openTo']
                )
            ),
            array(
                    'OR:openFrom:>=' => $scriptProperties['openFrom'],
                    'openTo:<=' => $scriptProperties['openTo']
                )
        ),
        'idItem' => $scriptProperties['idItem'],
        'openDay' => $scriptProperties['openDay'],
    )
);

that is making:

( 
	( 
		( 
			( 
				`openFrom` <= '0:00' 
				AND 
				`openTo` >= '0:00' 
			) 
			OR 
			( 
				`openFrom` <= '22:00' 
				AND 
				`openTo` >= '22:00' 
			) 
		) 
		OR 
		( 
			`openFrom` >= '0:00' 
			AND 
			`openTo` <= '22:00' 
		) 
	) 
	AND 
	`idItem` = 1 
	AND 
	`openDay` = 0 
)

correct

thx again

--
John
@theboxer