Thread

Hello!  I am trying to create a real estate list that orders the properties based on the size.  The size would order from the largest to the smallest down the list.

Below is the code I am using to pull those property entries:

{exp:channel:entries channel="properties1|properties2" category="19"  orderby="total_sf1|total_sf2" sort="desc|desc" dynamic="off"}

However, the outcome of this code is seen in the attached image.

The orange highlighted are properties from the “properties1” channel and the green highlighted are properties from the “properties2” channel.

Is it not possible to combine the ordering and sorting from both channels so that it orders the properties from largest to smallest for all entries?  Is there a way that I can perhaps pull the entries by category instead and still be able to sort the properties from largest to smallest size?

I appreciate any help you can provide me!

Thank you!

try to use Query Module

tried using the query module

correct me if i’m wrong, but i’m beginning to see that what i am trying to achieve is not possible

or does anybody else have a solution to what they would try?

Hi Tomotechi,

Ahmad is right. You can achieve it with proper SQL query. Would be better if you can share you sql here.

Best Regards,

Hello. Below is my code I used to just pull the square footage from a single channel id and ordered it desc:

{exp:query sql="SELECT field_id_24 FROM exp_channel_data WHERE channel_id = '5' ORDER BY field_id_24 DESC"}
{field_id_24}

{/exp:query}

This code above worked great pulling the square footage information and ordering it from largest to smallest… but only for one channel!

I do not know how to also SELECT field_id_177 WHERE channel_id =‘14’ and ORDER BY both field_id_24 & field_id_177 DESC.

I hope this makes sense.

you can use this SQL statment:

select x from (SELECT field_id_24 as x FROM exp_channel_data WHERE channel_id = '5' UNION ALL SELECT field_id_177 as x FROM exp_channel_data WHERE channel_id ='14')as x_t order by x_t.x desc

try it and let me know

Hello Ahmad.  Thank you for trying to help me.  I am trying to figure out what the variable needs to be in between the query tag pairs.  At the moment all I see is {field_id_24} repeating itself all the way down.  So your code may work but without the correct variable, I am not able to tell.  Can you help me? Many thanks!

try to use {x} or you can change the code to :

select x as size from (SELECT field_id_24 as x FROM exp_channel_data WHERE channel_id = '5' UNION ALL SELECT field_id_177 as x FROM exp_channel_data WHERE channel_id ='14')as x_t order by x_t.x desc

then use the {size} var

Hello Ahmad.  I forgot to mention that I did try {x} and it also repeated itself all the way down.  I will try the revised query statement as soon as I can get back to my machine and let you know what it pulls.  Thank you so much for your help so far.  It’s been very informative.

u welcome but I don’t understand what u mean “it repeated itself all the way down” did u mean the {x} not parssing or what.

I try to use this code on my system and it’s word perfctlly.

Hello Ahmad.  In the first statement, the {x} did not parse.

The 2nd query statement using the {size} variable worked perfectly!!!

THANK YOU!!!

Hello Ahmad.

After looking at your statement, I was also able to get the same result using this query statement:

{exp:query sql="SELECT field_id_24 total_size FROM exp_channel_data WHERE channel_id='5' UNION ALL SELECT field_id_177 total_size FROM exp_channel_data WHERE channel_id='14' ORDER BY total_size DESC"}
{total_size}

{/exp:query}

I really appreciate the help you have given me to better understand the query module!

I just realised that the Title, which is the name of the property, is part of the exp_channel_titles table.  How can I get the query statement to also include title FROM exp_channel_titles WHERE channel_id=‘5’ and title FROM exp_channel_titles WHERE channel_id=‘14’? 

Basically I need to pull the title and square footage information together so that

{title} - {total_size}

will display something like

Northbrook Mall - 95000
Sharpstown Centre - 88000
Fannin Shopping Centre - 50000

I tried the following code but ended up getting an error

{exp:query limit="20" paginate="top" sql="SELECT title, field_id_21, field_id_57, field_id_58, field_id_23, field_id_24 total_size 
FROM exp_channel_titles, exp_channel_data 
WHERE channel_id='5' 
UNION ALL 
SELECT title, field_id_170, field_id_171, field_id_172, field_id_176, field_id_177 total_size 
FROM exp_channel_titles, exp_channel_data 
WHERE channel_id='14' 
ORDER BY total_size DESC"}

Error Number: 1052

Column ‘channel_id’ in where clause is ambiguous

I think u must add this for you where clause:

WHERE exp_channel_titles.entry_id=exp_channel_data.entry_id
and   exp_channel_data.channel_id='5'

this error happened because the both table have the same name for tow column , so you must specifed what the column the where clause must check.

I hope this will help u.

Ahmad… you are AWESOME!!!!!!!!!

I am almost there… I still have to further filter my query to pull from a specific category.  If I need more assistance, I do hope you will be available to help with that.

Thank you thank you thank you!!!