programing

쿼리에서 연속 제한 및 오프셋이 잘못 배치되었습니다.

lastcode 2023. 9. 4. 20:07
반응형

쿼리에서 연속 제한 및 오프셋이 잘못 배치되었습니다.

나는 노드 Js에서 속편화를 사용하고 있으며 다음 코드를 가지고 있습니다.

Time_Sheet_Details.findAll({
include: [
    {
        model: timesheetNotesSubcon,
        required: false,
        attributes:["note","file_name", "id", "working_hrs", "timestamp", "has_screenshot", "notes_category"]
    },
    {
        model: Timesheet,
        attributes:["id","leads_id","userid"],
        include:[
            {
                model: Lead_Info, attributes:["id","fname","lname","email","hiring_coordinator_id","status"],
                where: { hiring_coordinator_id : 326},
                include:[{
                    model: adminInfoSchema,
                    required: false,
                    attributes:["admin_id","admin_fname", "admin_lname", "admin_email", "signature_contact_nos", "signature_company"],      
                }]
                
            },
            {model:Personal_Info,attributes:["userid","fname","lname","email"]}
        ],
    }],
where: { 
    reference_date: filters.reference_date
},
order:[
    ["id","DESC"]
],
offset:((1-1)*30),
limit : 30,

}).then(function(foundObject){
    willFulfillDeferred.resolve(foundObject);
});

결과 쿼리는 다음과 같습니다.

 SELECT `timesheet_details`.*, `timesheet_notes_subcons`.`note` AS
 `timesheet_notes_subcons.note`, `timesheet_notes_subcons`.`file_name` AS
 `timesheet_notes_subcons.file_name`, `timesheet_notes_subcons`.`id` AS
 `timesheet_notes_subcons.id`, `timesheet_notes_subcons`.`working_hrs` AS
 `timesheet_notes_subcons.working_hrs`, `timesheet_notes_subcons`.`timestamp` AS 
`timesheet_notes_subcons.timestamp`, `timesheet_notes_subcons`.`has_screenshot` AS 
`timesheet_notes_subcons.has_screenshot`, 
`timesheet_notes_subcons`.`notes_category` AS
 `timesheet_notes_subcons.notes_category`, `timesheet.lead`.`id` AS
 `timesheet.lead.id`, `timesheet.lead`.`fname` AS `timesheet.lead.fname`,
 `timesheet.lead`.`lname` AS `timesheet.lead.lname`,
`timesheet.lead`.`email` AS `timesheet.lead.email`,
 `timesheet.lead`.`hiring_coordinator_id` AS
 `timesheet.lead.hiring_coordinator_id`, `timesheet.lead`.`status` AS
 `timesheet.lead.status`, `timesheet.lead.admin`.`admin_id` AS
 `timesheet.lead.admin.admin_id`, `timesheet.lead.admin`.`admin_fname` AS 
`timesheet.lead.admin.admin_fname`, `timesheet.lead.admin`.`admin_lname` AS
 `timesheet.lead.admin.admin_lname`, `timesheet.lead.admin`.`admin_email` AS
 `timesheet.lead.admin.admin_email`, 
`timesheet.lead.admin`.`signature_contact_nos` AS
 `timesheet.lead.admin.signature_contact_nos`,
 `timesheet.lead.admin`.`signature_company` AS
 `timesheet.lead.admin.signature_company`, `timesheet.personal`.`userid` AS
 `timesheet.personal.userid`, `timesheet.personal`.`fname` AS
 `timesheet.personal.fname`, `timesheet.personal`.`lname` AS
 `timesheet.personal.lname`, `timesheet.personal`.`email` AS
 `timesheet.personal.email` FROM (SELECT `timesheet_details`.`id`,
 `timesheet_details`.`timesheet_id`, `timesheet_details`.`day`,
 `timesheet_details`.`total_hrs`, `timesheet_details`.`adj_hrs`,
 `timesheet_details`.`regular_rostered`, `timesheet_details`.`hrs_charged_to_client`,
 `timesheet_details`.`diff_charged_to_client`,
 `timesheet_details`.`hrs_to_be_subcon`, 
`timesheet_details`.`diff_paid_vs_adj_hrs`, `timesheet_details`.`status`,
 `timesheet_details`.`reference_date`, `timesheet`.`id` AS `timesheet.id`,
 `timesheet`.`leads_id` AS `timesheet.leads_id`, `timesheet`.`userid` AS
 `timesheet.userid` FROM `timesheet_details` AS `timesheet_details` 
LEFT OUTER JOIN `timesheet` AS `timesheet` 
ON `timesheet_details`.`timesheet_id` = `timesheet`.`id` 
WHERE (`timesheet_details`.`reference_date` >= '2016-04-23 16:00:00' 
AND `timesheet_details`.`reference_date` < '2017-05-02 15:59:59') 
ORDER BY `timesheet_details`.`id` DESC LIMIT 0, 30) AS
 `timesheet_details` LEFT OUTER JOIN `timesheet_notes_subcon` AS
 `timesheet_notes_subcons` ON `timesheet_details`.`id` =
 `timesheet_notes_subcons`.`timesheet_details_id` 
INNER JOIN `leads` AS `timesheet.lead` ON `timesheet.leads_id` =
 `timesheet.lead`.`id` AND `timesheet.lead`.`hiring_coordinator_id` = 326
 LEFT OUTER JOIN `admin` AS `timesheet.lead.admin` ON
 `timesheet.lead`.`hiring_coordinator_id` =
 `timesheet.lead.admin`.`admin_id` 
LEFT OUTER JOIN `personal` AS `timesheet.personal` ON `timesheet.userid`
 = `timesheet.personal`.`userid` ORDER BY `timesheet_details`.`id` DESC;

보다시피,LIMIT 0, 30쿼리의 끝에 없습니다.이 문제는 해당 쿼리가 아무것도 반환하지 않으며 제한과 오프셋은 다음과 같이 쿼리의 끝에 있어야 하기 때문입니다.

 SELECT `timesheet_details`.*, `timesheet_notes_subcons`.`note` AS
`timesheet_notes_subcons.note`, `timesheet_notes_subcons`.`file_name` AS
 `timesheet_notes_subcons.file_name`, `timesheet_notes_subcons`.`id` AS
 `timesheet_notes_subcons.id`, `timesheet_notes_subcons`.`working_hrs` AS
 `timesheet_notes_subcons.working_hrs`,
 `timesheet_notes_subcons`.`timestamp` AS
 `timesheet_notes_subcons.timestamp`,
 `timesheet_notes_subcons`.`has_screenshot` AS
 `timesheet_notes_subcons.has_screenshot`,
 `timesheet_notes_subcons`.`notes_category` AS
 `timesheet_notes_subcons.notes_category`, `timesheet.lead`.`id` AS
 `timesheet.lead.id`, `timesheet.lead`.`fname` AS `timesheet.lead.fname`,
 `timesheet.lead`.`lname` AS `timesheet.lead.lname`,
 `timesheet.lead`.`email` AS `timesheet.lead.email`,
 `timesheet.lead`.`hiring_coordinator_id` AS
 `timesheet.lead.hiring_coordinator_id`, `timesheet.lead`.`status` AS
 `timesheet.lead.status`, `timesheet.lead.admin`.`admin_id` AS
 `timesheet.lead.admin.admin_id`, `timesheet.lead.admin`.`admin_fname` AS
 `timesheet.lead.admin.admin_fname`, `timesheet.lead.admin`.`admin_lname`
 AS `timesheet.lead.admin.admin_lname`,
 `timesheet.lead.admin`.`admin_email` AS
 `timesheet.lead.admin.admin_email`,
 `timesheet.lead.admin`.`signature_contact_nos` AS
 `timesheet.lead.admin.signature_contact_nos`,
 `timesheet.lead.admin`.`signature_company` AS
 `timesheet.lead.admin.signature_company`, `timesheet.personal`.`userid`
 AS `timesheet.personal.userid`, `timesheet.personal`.`fname` AS
 `timesheet.personal.fname`, `timesheet.personal`.`lname` AS
 `timesheet.personal.lname`, `timesheet.personal`.`email` AS
 `timesheet.personal.email` FROM (SELECT `timesheet_details`.`id`,
 `timesheet_details`.`timesheet_id`, `timesheet_details`.`day`,
 `timesheet_details`.`total_hrs`, `timesheet_details`.`adj_hrs`,
 `timesheet_details`.`regular_rostered`,
 `timesheet_details`.`hrs_charged_to_client`,
 `timesheet_details`.`diff_charged_to_client`,
 `timesheet_details`.`hrs_to_be_subcon`,
 `timesheet_details`.`diff_paid_vs_adj_hrs`,
 `timesheet_details`.`status`, `timesheet_details`.`reference_date`,
 `timesheet`.`id` AS `timesheet.id`, `timesheet`.`leads_id` AS
 `timesheet.leads_id`, `timesheet`.`userid` AS `timesheet.userid` 
FROM `timesheet_details` AS `timesheet_details` 
LEFT OUTER JOIN `timesheet` AS `timesheet` ON
 `timesheet_details`.`timesheet_id` = `timesheet`.`id` 
WHERE (`timesheet_details`.`reference_date` >= '2016-04-23 16:00:00' 
AND `timesheet_details`.`reference_date` < '2017-05-02 15:59:59') 
ORDER BY `timesheet_details`.`id` DESC) AS `timesheet_details` 
LEFT OUTER JOIN `timesheet_notes_subcon` AS `timesheet_notes_subcons` ON
 `timesheet_details`.`id` =
 `timesheet_notes_subcons`.`timesheet_details_id` INNER JOIN `leads` AS
 `timesheet.lead` ON `timesheet.leads_id` = `timesheet.lead`.`id` AND 
`timesheet.lead`.`hiring_coordinator_id` = 326 LEFT OUTER JOIN `admin` AS
 `timesheet.lead.admin` ON `timesheet.lead`.`hiring_coordinator_id` =
 `timesheet.lead.admin`.`admin_id` LEFT OUTER JOIN `personal` AS
 `timesheet.personal` ON `timesheet.userid` =
 `timesheet.personal`.`userid` ORDER BY `timesheet_details`.`id` DESC
 LIMIT 0, 30;

내 코드에 내가 잘못하고 있는 것이 있습니까?제가 주문과 한도를 잘못 입력했나요?

제 질문에 대한 답을 찾았습니다. 추가하기만 하면 됩니다.subQuery = false제한 및 오프셋이 하위 쿼리로 평가되지 않도록 합니다.그리고 오프셋과 한계도 쿼리의 끝에 있습니다.

offset:((page-1)*limit),
limit : limit,
subQuery:false

주문을 해야 하며 앞의 절에 포함된 내용이 포함됩니다.이런 거 해주세요.

 user.findAll({
             offset: 5, limit: 5,
            order: [
// Will escape full_name and validate DESC against a list of valid direction parameters
['full_name', 'DESC']]
        }).then(function (result) {
})

결과 쿼리는 다음과 같습니다.

enter image description here

만약 당신이 include에 주문을 넣고 싶다면, 당신은 include part에 주문을 넣어야 합니다.

include: [{
                model: taskhelpers, required: true,
                order: {
                    order: '`updatedAt` ASC'
                }
           }]

자세한 내용은 페이지/제한 및 순서 확인

업데이트된 중첩 포함, 제한 및 순서

var option = {
                offset: 5, limit: 5,
            order: [
// Will escape full_name and validate DESC against a list of valid direction parameters
['id', 'DESC']],
                attributes: [
                    'id', 'title',
                    [sequelize.Sequelize.fn('date_format', sequelize.Sequelize.col('date'), '%d-%b-%Y'), 'date']
                ],

                include: [

                    {
                        model: taskhelpers, required: true,

                        where: {
                            userId: req.params.userid,

                            $or: [
                                {
                                    status: {
                                        $eq: "1"
                                    }
                                },
                                {
                                    status: {
                                        $eq: "3"
                                    }
                                },
                            ]
                        }

                    }]
            };

이제 이 옵션을 모델 매개 변수로 전달합니다.

tasks.findAll(options)
    .then(function (result) {
        res.send({message:result,error:null});
    })
        .catch(function (err) {
            res.send({message:null,error:err});
        })

생성된 쿼리가 됩니다.

enter image description here

동일한 경우가 있습니다. 다른 방법은 배열을 제한으로 전달하는 것입니다.

{
include: [
    {
        model: timesheetNotesSubcon,
        required: false,
        attributes:["note","file_name", "id", "working_hrs", "timestamp", "has_screenshot", "notes_category"]
    },
    {
        model: Timesheet,
        attributes:["id","leads_id","userid"],
        include:[
            {
                model: Lead_Info, attributes:["id","fname","lname","email","hiring_coordinator_id","status"],
                where: { hiring_coordinator_id : 326},
                include:[{
                    model: adminInfoSchema,
                    required: false,
                    attributes:["admin_id","admin_fname", "admin_lname", "admin_email", "signature_contact_nos", "signature_company"],      
                }]

            },
            {model:Personal_Info,attributes:["userid","fname","lname","email"]}
        ],
    }],
where: { 
    reference_date: filters.reference_date
},
order:[
    ["id","DESC"]
],
limit : [((page-1)*limit), limit],

}

Node.js에서 sequilize를 사용한 제한, 오프셋, 필드 투영 및 전체 텍스트 검색


        let msg = await Feedback.findAll({
            where: Sequelize.literal('MATCH (message) AGAINST (:feedback)'), // fulltext search query
            replacements: {
            feedback: req.body.text  
            },
            attributes: ['uID', 'message'], // projection of columns
            offset:1, // set the offset according your use case
            limit: 1  // limit the output
        });

요청에 $limit 매개 변수를 추가하려고 하면 sufficialize가 자동으로 사용됩니다. "ex://localhost:3031/my-service?$limit=23"

언급URL : https://stackoverflow.com/questions/43729254/sequelize-limit-and-offset-incorrect-placement-in-query

반응형

'programing' 카테고리의 다른 글

Swift - 두 줄의 텍스트가 있는 UI 버튼  (0) 2023.09.04
python의 __init_ 상속 및 재정의  (0) 2023.09.04
모달의 부트스트랩 3 및 유튜브  (0) 2023.09.04
왜 악의적인 사이트는 공격하기 전에 GET를 통해 CSRF 토큰을 얻을 수 없습니까?  (0) 2023.09.04
MariaDB 타임스탬프가 업데이트 날짜가 아닙니다.  (0) 2023.09.04

'programing'의 다른글

  • 현재글쿼리에서 연속 제한 및 오프셋이 잘못 배치되었습니다.

관련글

댓글

티스토리툴바