• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

So tired solving this problem, Help please

New Here ,
Jul 04, 2019 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 ?

Views

309

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
community guidelines

correct answers 1 Correct answer

Community Expert , Jul 04, 2019 Jul 04, 2019

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 an

...

Votes

Translate

Translate
Community Expert ,
Jul 04, 2019 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 (troubleshooter, carehart.org)

Votes

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
community guidelines
New Here ,
Jul 04, 2019 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

Votes

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
community guidelines
Community Expert ,
Jul 05, 2019 Jul 05, 2019

Copy link to clipboard

Copied

LATEST

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


/Charlie (troubleshooter, carehart.org)

Votes

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
community guidelines
Resources
Documentation