Subquery for retrieving count












2















I'm trying to select all parents with the count of their children.



I have the following query:



SELECT 
a.*,
(SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


My documents look similar to:



xyz:1



{
id: 1,
name: "parent",
createdAt: 1234
}


xyz:2



{
id: 2,
name: "child",
parent: "parent",
createdAt: 5678
}


I get the below error:




Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.



Error code: 5010




UPDATE:
The below query seem to work:



SELECT 
a.*,
(SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
FROM demo a
WHERE
meta(a).id LIKE "xyz:%"
AND a.parent IS MISSING
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


but if I replace "parent" with a.name it gives the same error.










share|improve this question





























    2















    I'm trying to select all parents with the count of their children.



    I have the following query:



    SELECT 
    a.*,
    (SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
    FROM demo a
    WHERE
    meta(a).id LIKE "xyz:%"
    AND a.parent IS MISSING
    ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


    My documents look similar to:



    xyz:1



    {
    id: 1,
    name: "parent",
    createdAt: 1234
    }


    xyz:2



    {
    id: 2,
    name: "child",
    parent: "parent",
    createdAt: 5678
    }


    I get the below error:




    Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.



    Error code: 5010




    UPDATE:
    The below query seem to work:



    SELECT 
    a.*,
    (SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
    FROM demo a
    WHERE
    meta(a).id LIKE "xyz:%"
    AND a.parent IS MISSING
    ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


    but if I replace "parent" with a.name it gives the same error.










    share|improve this question



























      2












      2








      2








      I'm trying to select all parents with the count of their children.



      I have the following query:



      SELECT 
      a.*,
      (SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
      FROM demo a
      WHERE
      meta(a).id LIKE "xyz:%"
      AND a.parent IS MISSING
      ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


      My documents look similar to:



      xyz:1



      {
      id: 1,
      name: "parent",
      createdAt: 1234
      }


      xyz:2



      {
      id: 2,
      name: "child",
      parent: "parent",
      createdAt: 5678
      }


      I get the below error:




      Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.



      Error code: 5010




      UPDATE:
      The below query seem to work:



      SELECT 
      a.*,
      (SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
      FROM demo a
      WHERE
      meta(a).id LIKE "xyz:%"
      AND a.parent IS MISSING
      ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


      but if I replace "parent" with a.name it gives the same error.










      share|improve this question
















      I'm trying to select all parents with the count of their children.



      I have the following query:



      SELECT 
      a.*,
      (SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count
      FROM demo a
      WHERE
      meta(a).id LIKE "xyz:%"
      AND a.parent IS MISSING
      ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


      My documents look similar to:



      xyz:1



      {
      id: 1,
      name: "parent",
      createdAt: 1234
      }


      xyz:2



      {
      id: 2,
      name: "child",
      parent: "parent",
      createdAt: 5678
      }


      I get the below error:




      Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.



      Error code: 5010




      UPDATE:
      The below query seem to work:



      SELECT 
      a.*,
      (SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count
      FROM demo a
      WHERE
      meta(a).id LIKE "xyz:%"
      AND a.parent IS MISSING
      ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0


      but if I replace "parent" with a.name it gives the same error.







      couchbase n1ql






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 3 at 16:44









      Johan Larson

      1,4951813




      1,4951813










      asked Jan 3 at 8:04









      Niket MalikNiket Malik

      6801920




      6801920
























          1 Answer
          1






          active

          oldest

          votes


















          2














          You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:



          SELECT l.name, COUNT(1)
          FROM demo l JOIN demo r ON r.name = l.child
          GROUP BY l.name


          To run this query you will need an index on the r side:



          CREATE INDEX demo_name_idx ON demo(name)


          Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.






          share|improve this answer


























          • I'm getting an error { "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }

            – Niket Malik
            Jan 7 at 13:08











          • Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.

            – Johan Larson
            Jan 7 at 13:51











          • My couchbase version: Community Edition 5.0.1 build 5003, I guess this does not support?

            – Niket Malik
            Jan 7 at 14:46













          • You need 5.5 or later for ANSI joins.

            – Johan Larson
            Jan 7 at 17:17











          Your Answer






          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "1"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: true,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: 10,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54018421%2fsubquery-for-retrieving-count%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          2














          You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:



          SELECT l.name, COUNT(1)
          FROM demo l JOIN demo r ON r.name = l.child
          GROUP BY l.name


          To run this query you will need an index on the r side:



          CREATE INDEX demo_name_idx ON demo(name)


          Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.






          share|improve this answer


























          • I'm getting an error { "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }

            – Niket Malik
            Jan 7 at 13:08











          • Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.

            – Johan Larson
            Jan 7 at 13:51











          • My couchbase version: Community Edition 5.0.1 build 5003, I guess this does not support?

            – Niket Malik
            Jan 7 at 14:46













          • You need 5.5 or later for ANSI joins.

            – Johan Larson
            Jan 7 at 17:17
















          2














          You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:



          SELECT l.name, COUNT(1)
          FROM demo l JOIN demo r ON r.name = l.child
          GROUP BY l.name


          To run this query you will need an index on the r side:



          CREATE INDEX demo_name_idx ON demo(name)


          Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.






          share|improve this answer


























          • I'm getting an error { "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }

            – Niket Malik
            Jan 7 at 13:08











          • Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.

            – Johan Larson
            Jan 7 at 13:51











          • My couchbase version: Community Edition 5.0.1 build 5003, I guess this does not support?

            – Niket Malik
            Jan 7 at 14:46













          • You need 5.5 or later for ANSI joins.

            – Johan Larson
            Jan 7 at 17:17














          2












          2








          2







          You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:



          SELECT l.name, COUNT(1)
          FROM demo l JOIN demo r ON r.name = l.child
          GROUP BY l.name


          To run this query you will need an index on the r side:



          CREATE INDEX demo_name_idx ON demo(name)


          Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.






          share|improve this answer















          You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:



          SELECT l.name, COUNT(1)
          FROM demo l JOIN demo r ON r.name = l.child
          GROUP BY l.name


          To run this query you will need an index on the r side:



          CREATE INDEX demo_name_idx ON demo(name)


          Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 7 at 13:50

























          answered Jan 3 at 13:09









          Johan LarsonJohan Larson

          1,4951813




          1,4951813













          • I'm getting an error { "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }

            – Niket Malik
            Jan 7 at 13:08











          • Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.

            – Johan Larson
            Jan 7 at 13:51











          • My couchbase version: Community Edition 5.0.1 build 5003, I guess this does not support?

            – Niket Malik
            Jan 7 at 14:46













          • You need 5.5 or later for ANSI joins.

            – Johan Larson
            Jan 7 at 17:17



















          • I'm getting an error { "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }

            – Niket Malik
            Jan 7 at 13:08











          • Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.

            – Johan Larson
            Jan 7 at 13:51











          • My couchbase version: Community Edition 5.0.1 build 5003, I guess this does not support?

            – Niket Malik
            Jan 7 at 14:46













          • You need 5.5 or later for ANSI joins.

            – Johan Larson
            Jan 7 at 17:17

















          I'm getting an error { "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }

          – Niket Malik
          Jan 7 at 13:08





          I'm getting an error { "code": 3000, "msg": "syntax error - at r", "query_from_user": "SELECT l.name, COUNT(1) FROM demo l JOIN demo r ON r.name = l.child GROUP BY l.name" }

          – Niket Malik
          Jan 7 at 13:08













          Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.

          – Johan Larson
          Jan 7 at 13:51





          Niket, are you using a very old version of Couchbase? ANSI joins like this are recent functionality. Also, I changed the index, above.

          – Johan Larson
          Jan 7 at 13:51













          My couchbase version: Community Edition 5.0.1 build 5003, I guess this does not support?

          – Niket Malik
          Jan 7 at 14:46







          My couchbase version: Community Edition 5.0.1 build 5003, I guess this does not support?

          – Niket Malik
          Jan 7 at 14:46















          You need 5.5 or later for ANSI joins.

          – Johan Larson
          Jan 7 at 17:17





          You need 5.5 or later for ANSI joins.

          – Johan Larson
          Jan 7 at 17:17




















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Stack Overflow!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54018421%2fsubquery-for-retrieving-count%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Monofisismo

          Angular Downloading a file using contenturl with Basic Authentication

          Olmecas