{"id":15705,"date":"2024-05-09T10:03:54","date_gmt":"2024-05-09T17:03:54","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=15705"},"modified":"2024-05-22T13:47:54","modified_gmt":"2024-05-22T20:47:54","slug":"query-graph-recursive-cte","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/query-graph-recursive-cte\/","title":{"rendered":"Query Graph Models With Couchbase Recursive CTE"},"content":{"rendered":"

Recursive Common Table Expressions (CTEs) and Oracle’s CONNECT BY are well-known SQL constructs among RDBMS users, facilitating the delegation of complex, interdependent data structure exploration to the database layer for enhanced processing efficiency.<\/span><\/p>\n

These constructs are crucial for querying interdependent data structures, a common requirement across various industries, including finance, supply chain management, customer relationship management (CRM), travel booking, and more recently, social networks. Recognizing their importance, all major relational database management systems (RDBMS) such as PostgreSQL, MySQL (from version 8.0), SQL Server, Oracle, and SQLite offer support for Recursive CTEs.<\/span><\/p>\n

In contrast, NoSQL databases, which are designed to manage a diverse array of data models like documents, key-value, wide-column, and graph data, prioritize scalability, high availability, flexibility, and performance in distributed systems. In these environments, the CTE concept, recursive or not, isn’t directly addressed. Users often turn to specialized solutions such as graph databases\u2014Cypher for Neo4J and AQL for ArangoDB, for instance\u2014to handle complex data structures.<\/span><\/p>\n

Couchbase sets itself apart with SQL for JSON, offering a unique approach to Recursive CTEs that also extend its multi model support. This blog will delve into three primary topics:<\/span><\/p>\n

    \n
  1. \n
      \n
    1. How you can leverage a single DBMS as Couchbase for complex data structures for a number of use cases, in the same way as RDBMS could, but without the need for a more dedicated database.\u00a0<\/span><\/li>\n
    2. The use of Couchase SQL++ construct to query, transform and project these complex relationship using a SQL construct that are familiar to RDBMS users.<\/span><\/li>\n
    3. Best practices to manage resource consumption with Recursive CTE.<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

      Bill of Materials use case<\/span><\/h2>\n

      The BOM is a critical component in manufacturing and engineering, detailing the raw materials, parts, and components needed to manufacture a product. It often has a hierarchical structure, where parts are made up of other parts or materials.<\/span><\/p>\n

      This example will include basic components and sub-components of a desktop computer.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
      Component ID<\/b><\/td>\nComponentName<\/b><\/td>\nParentComponentID<\/b><\/td>\nQuantity<\/b><\/td>\n<\/tr>\n
      1<\/span><\/td>\nDesktop Computer<\/span><\/td>\nnull<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      2<\/span><\/td>\nMotherboard<\/span><\/td>\n1<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      3<\/span><\/td>\nCPU<\/span><\/td>\n2<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      3<\/span><\/td>\nCPU fan<\/span><\/td>\n3<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      4<\/span><\/td>\nGPU<\/span><\/td>\n2<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      5<\/span><\/td>\nRAM<\/span><\/td>\n2<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n
      6<\/span><\/td>\nM.2 drive<\/span><\/td>\n2<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      7<\/span><\/td>\nSSD<\/span><\/td>\n2<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      8<\/span><\/td>\nPower Supply<\/span><\/td>\n1<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      9<\/span><\/td>\nCase<\/span><\/td>\n1<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
      10<\/span><\/td>\nCase cooling fans<\/span><\/td>\n1<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

      Let’s say we want to list all parts and sub-parts needed to build a desktop computer, along with the quantities required for each.<\/span><\/p>\n

      WITH RECURSIVE ComponentHierarchy AS (\r\n\u00a0 \u00a0 SELECT\r\n\u00a0 \u00a0 \u00a0 \u00a0 ComponentID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 ComponentName,\r\n\u00a0 \u00a0 \u00a0 \u00a0 ParentComponentID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 Quantity,\r\n\u00a0 \u00a0 \u00a0 \u00a0 1 AS Lvl -- Depth level in the hierarchy\r\n\u00a0 \u00a0 FROM\r\n\u00a0 \u00a0 \u00a0 \u00a0 components\r\n\u00a0 \u00a0 WHERE\r\n\u00a0 \u00a0 \u00a0 \u00a0 ParentComponentID IS NULL -- Starting point: the Desktop Computer itself\r\n\r\n\u00a0 \u00a0 UNION ALL\r\n\r\n\u00a0 \u00a0 SELECT\r\n\u00a0 \u00a0 \u00a0 \u00a0 c.ComponentID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 c.ComponentName,\r\n\u00a0 \u00a0 \u00a0 \u00a0 c.ParentComponentID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 ch.Quantity * c.Quantity AS Quantity, -- Calculate total quantity required at each level\r\n\u00a0 \u00a0 \u00a0 \u00a0 ch.lvl + 1 -- Increment level for each recursion step\r\n\u00a0 \u00a0 FROM\r\n\u00a0 \u00a0 \u00a0 \u00a0 components c\r\n\u00a0 \u00a0 JOIN\r\n\u00a0 \u00a0 \u00a0 \u00a0 ComponentHierarchy ch ON c.ParentComponentID = ch.ComponentID\r\n)\r\nSELECT * FROM ComponentHierarchy;<\/pre>\n
      This query initializes the recursion with the top-level item (the Desktop Computer) and recursively joins the components table with itself to traverse down the hierarchy, adjusting quantities as needed for each component and sub-component.<\/span><\/p>\n
      The result will list all parts needed for the Desktop Computer, including the quantity of each and their hierarchical level, which can be useful for understanding the assembly structure or for inventory and ordering purposes.<\/span><\/p>\n
      Explanation<\/span><\/h3>\n
      The CTE starts with the Bicycle (where ParentPartID is NULL) and then recursively finds all components and sub-components.<\/span><\/p>\n
      The Quantity is adjusted at each level to reflect the total number required for one Bicycle.<\/span><\/p>\n
      The Level column, though not strictly necessary for all BOM analyses, helps understand the depth of each part within the hierarchy.<\/span><\/p>\n
      This approach allows for a detailed breakdown of all materials and components required for manufacturing a product, essential for inventory management, cost estimation, and production planning in manufacturing operations.<\/span><\/p>\n
      Social Networking use case<\/span><\/h2>\n
      A common application in a social networking use case is to find the degrees of connection between two users\u2014essentially, how users are connected through a chain of mutual friends. Let’s say we need to determine the shortest path (in terms of degrees of connection) between two users in a social network. This can help in features like suggesting friendships or understanding network dynamics.<\/span><\/p>\n
      Consider we have the following users and how they are friends with each other, eg. Alice is friends with Bob, and also with Charlie. But Alice is not friends with Dana.<\/span><\/p>\n

      \"\"

      <\/p>\n
      In this expanded network, users are connected in various ways, creating multiple paths through which users can be connected.<\/span><\/p>\n
      Let’s find the degrees of connection between Alice[1] and Frank[6], including the names of users along the path.<\/span><\/p>\n
      WITH RECURSIVE ConnectionPath AS (\r\n\u00a0 \u00a0 SELECT\r\n\u00a0 \u00a0 \u00a0 \u00a0 u1.UserID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 u1.UserName AS StartUser,\r\n\u00a0 \u00a0 \u00a0 \u00a0 u2.UserID AS FriendID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 u2.UserName AS FriendName,\r\n\u00a0 \u00a0 \u00a0 \u00a0 1 AS Degree\r\n\u00a0 \u00a0 FROM\r\n\u00a0 \u00a0 \u00a0 \u00a0 user_connections uc\r\n\u00a0 \u00a0 JOIN\r\n\u00a0 \u00a0 \u00a0 \u00a0 users u1 ON uc.UserID = u1.UserID\r\n\u00a0 \u00a0 JOIN\r\n\u00a0 \u00a0 \u00a0 \u00a0 users u2 ON uc.FriendID = u2.UserID\r\n\u00a0 \u00a0 WHERE\r\n\u00a0 \u00a0 \u00a0 \u00a0 u1.UserID = 1 -- Starting user (Alice)\r\n\u00a0 \u00a0 \r\n\u00a0 \u00a0 UNION ALL\r\n\u00a0 \u00a0 \r\n\u00a0 \u00a0 SELECT\r\n\u00a0 \u00a0 \u00a0 \u00a0 cp.UserID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 cp.StartUser,\r\n\u00a0 \u00a0 \u00a0 \u00a0 u.UserID AS FriendID,\r\n\u00a0 \u00a0 \u00a0 \u00a0 u.UserName AS FriendName,\r\n\u00a0 \u00a0 \u00a0 \u00a0 cp.Degree + 1\r\n\u00a0 \u00a0 FROM\r\n\u00a0 \u00a0 \u00a0 \u00a0 ConnectionPath cp\r\n\u00a0 \u00a0 JOIN\r\n\u00a0 \u00a0 \u00a0 \u00a0 user_connections uc ON cp.FriendID = uc.UserID\r\n\u00a0 \u00a0 JOIN\r\n\u00a0 \u00a0 \u00a0 \u00a0 users u ON uc.FriendID = u.UserID\r\n\u00a0 \u00a0 WHERE\r\n\u00a0 \u00a0 \u00a0 \u00a0 uc.FriendID NOT IN (cp.UserID) -- Avoid loops by not revisiting the start user\r\n)\r\nSELECT * FROM ConnectionPath\r\nWHERE FriendID = 6 -- Target user (Frank)\r\nORDER BY Degree ASC;<\/pre>\n
      Explanation<\/span><\/h3>\n