Highlighted

So tired solving this problem, Help please

New Here ,
Jul 04, 2019

Copy link to clipboard

Copied

This is my table for messages

Screenshot 2019-07-04 at 4.00.43 PM.png

I'm trying to pull data from only mid no 1 and mid no 4, basically my goal is to get the last message from every sender

First i created a query to pull all the message for a user, in this case receiver id is 1

<cfquery name="messages_tb" datasource="appdb">

    SELECT * FROM messages

    WHERE receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">

</cfquery>

then i created a list of every sender id

<cfset list.messages_tb_sender_uid = ValueList(messages_tb.sender)>

after that i did this

<cfquery name="messages_tb" datasource="appdb">

    SELECT MAX(mid) as last_id from messages

    WHERE sender IN ( <cfqueryparam value="#list.messages_tb_sender_uid#" cfsqltype="cf_sql_varchar" list="yes">)

    and receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">

</cfquery>

<cfset list.messages_tb_last = ValueList(messages_tb.last_id)>

<cfoutput>

    #list.messages_tb_last#

</cfoutput>

The problem is im getting only 1 row, i dont wanna use loop or output query. Is there any way to just create a list of last mid of every sender ?

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

If that last valuelist has only 1, then the messages_tb query has only one record.   Do a cfdump of the query  to confirm. So this is not a cfml problem but a sql one.

I would think you could do a simpler single select, whether with group by or subqueries, but a) I'd have to build out a test table to be sure my proposal was right and b) I'm on my phone.

My guess would be that this could work:

SELECT   sender, MAX(mid)

FROM      messages

GROUP BY sender

But if not, it could help if you used querynew and related functions to create a simulated query result, against which folks could then try to do a qofq in cfml, and in cffiddle.org or trycf.com, even from a phone. (Sure, we could "just do it". I'm proposing how you could help us help you.)

It may also help to know what dbms/version you're using, as some problems are best solved using db-specific features--as long as you don't favor cross db compatibility over efficiency. For instance, windowing function if in more recent versions of sql server can do amazing things.

Views

142

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

So tired solving this problem, Help please

New Here ,
Jul 04, 2019

Copy link to clipboard

Copied

This is my table for messages

Screenshot 2019-07-04 at 4.00.43 PM.png

I'm trying to pull data from only mid no 1 and mid no 4, basically my goal is to get the last message from every sender

First i created a query to pull all the message for a user, in this case receiver id is 1

<cfquery name="messages_tb" datasource="appdb">

    SELECT * FROM messages

    WHERE receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">

</cfquery>

then i created a list of every sender id

<cfset list.messages_tb_sender_uid = ValueList(messages_tb.sender)>

after that i did this

<cfquery name="messages_tb" datasource="appdb">

    SELECT MAX(mid) as last_id from messages

    WHERE sender IN ( <cfqueryparam value="#list.messages_tb_sender_uid#" cfsqltype="cf_sql_varchar" list="yes">)

    and receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">

</cfquery>

<cfset list.messages_tb_last = ValueList(messages_tb.last_id)>

<cfoutput>

    #list.messages_tb_last#

</cfoutput>

The problem is im getting only 1 row, i dont wanna use loop or output query. Is there any way to just create a list of last mid of every sender ?

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

If that last valuelist has only 1, then the messages_tb query has only one record.   Do a cfdump of the query  to confirm. So this is not a cfml problem but a sql one.

I would think you could do a simpler single select, whether with group by or subqueries, but a) I'd have to build out a test table to be sure my proposal was right and b) I'm on my phone.

My guess would be that this could work:

SELECT   sender, MAX(mid)

FROM      messages

GROUP BY sender

But if not, it could help if you used querynew and related functions to create a simulated query result, against which folks could then try to do a qofq in cfml, and in cffiddle.org or trycf.com, even from a phone. (Sure, we could "just do it". I'm proposing how you could help us help you.)

It may also help to know what dbms/version you're using, as some problems are best solved using db-specific features--as long as you don't favor cross db compatibility over efficiency. For instance, windowing function if in more recent versions of sql server can do amazing things.

Views

143

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 04, 2019 0
Adobe Community Professional ,
Jul 04, 2019

Copy link to clipboard

Copied

If that last valuelist has only 1, then the messages_tb query has only one record.   Do a cfdump of the query  to confirm. So this is not a cfml problem but a sql one.

I would think you could do a simpler single select, whether with group by or subqueries, but a) I'd have to build out a test table to be sure my proposal was right and b) I'm on my phone.

My guess would be that this could work:

SELECT   sender, MAX(mid)

FROM      messages

GROUP BY sender

But if not, it could help if you used querynew and related functions to create a simulated query result, against which folks could then try to do a qofq in cfml, and in cffiddle.org or trycf.com, even from a phone. (Sure, we could "just do it". I'm proposing how you could help us help you.)

It may also help to know what dbms/version you're using, as some problems are best solved using db-specific features--as long as you don't favor cross db compatibility over efficiency. For instance, windowing function if in more recent versions of sql server can do amazing things.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 04, 2019 0
New Here ,
Jul 04, 2019

Copy link to clipboard

Copied

Thank you soooo much, it works like a charm!

Thanks a ton man, You just made my year This problem was eating me alive ! not anymore

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 04, 2019 0
Adobe Community Professional ,
Jul 05, 2019

Copy link to clipboard

Copied

Very good to hear, and thanks for marking it as the answer.

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 05, 2019 0