CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.js
Armando Pacheco Ortiz
Armando Pacheco Ortiz

Reputation: 15

MySQL: Use Distinct Query, then join same table filtering with its result

After a few hours, and several dead-ends, I've decided to check with you guys. If there's a similar question/answer out there, and my lack of proper SQL understanding didn't help me get the gist of it, my apologies. But here goes:

My goal is to use one table to create a separated list of items for a checklist/to-do. Something like this:

 List title:
    |
    |---"First Entry Title" - "First Entry Body"
    |---"Second Entry Title" - "Second Entry Body"
    |--- Etc

I'm trying to do a Distinct search first, and use the results from that to filter the same table and join it with the alias 'items'. If there's a better way, then I wouldn't mind some assistance there. I just figured that would be the way about it.

I am currently using Sequelize, with the occasional raw query

I have a table of checklist entries, some rows share the same "parent", such as:

parent title body
First title1 body1
First title2 body2
Second title3 body3
Third title4 body4
Third title5 body5

My goal is to have a query result like this:

parent title body
First title1 body1
title2 body2
parent title body
Second title3 body3
parent title body
Third title4 body4
title5 body5

I realized a version of this was doable, when sequelize returned this to me in a different query:

{
"noteID":77,"noteContactID":"85","noteComment":"Test note","noteDate":1613429041844",
"Contact":{
     "contactID":85,"firstName":"Sully","middleName":null,"lastName":"Melland"
     }
}

However, this was done with two models, and likewise, two different tables. I was hoping to avoid that approach, so I wouldn't need to make a full table just to filter by the "parent"

 Note.findAll({
        where: {noteContactID: req.params.id},
        include: [Contact]
    })

List Example on project

Table structure in the database

Thank you in advance!

Upvotes: 1

Views: 175

Answers (1)

Ajinkya
Ajinkya

Reputation: 57

I can't think of a way where it will return one column name like 'first' and then give two rows of output in the next to columns. I think you can use a single column of type JSON to store {task-title:task-body}.

CREATE TABLE todo (Title varchar(255), Subtask JSON);

Then whenever you have any subtask, you can store it in JSON key-value pair. This you can store any number of subtasks, and you will need only 2 columns.

Upvotes: 1

Related Questions