Search SQL - rollthecloudinc/hedge GitHub Wiki

request:

  {"query": {
  "index": "classified-ad-location-category",
	"composite": {
		"location": "detroit",
		"category": "Vehicle"
	},
    "bool": {
      "all": [
				{
					"nested": {
						"path": "reviews",
						"ownership": 1,
						"additionalFields": ["feedback"],
						"bool": {
						  "all": [
									{
										"nested": {
											"path": "user",
											"additionalFields": ["username"],
											"bool": {
												"all": [
													{
														"term": {
															"field": "name",
															"value": "todd",
															"modifiers": {
																"operation": 0
															}
														}
													}
												]
											}
										}
									},
									{
									"filter": {
										"field": "reviewRating",
										"value": "3",
										"modifiers": {
										"operation": 0
									}
									}
								}
							]
					  }
					}
				}
			]
    }
	}
}

response:

{
  "sql": "SELECT\n        artifacts.id AS artifacts_id, \n        ((((0.0) + 0.0))) AS _score, \n        t_reviews_0.id AS reviews_id, \n        t_reviews_0.feedback AS reviews_feedback, \n        t_user_1.id AS user_id, \n        t_user_1.username AS user_username\n    FROM\n        artifacts artifacts\n        LEFT JOIN reviews t_reviews_0 ON t_reviews_0.artifacts_id = artifacts.id\nLEFT JOIN user t_user_1 ON t_reviews_0.user_id = t_user_1.id\n    WHERE\n        (((t_user_1.name = $1) AND t_reviews_0.reviewRating = $2))\n    \n\tORDER BY _score DESC\n\tLIMIT 10 OFFSET 0",
  "bindings": [
    "todd",
    "3"
  ],
  "hydrationSchema": {
    "IdentityColumn": "artifacts_id",
    "Fields": [
      {
        "ColumnAlias": "artifacts_id",
        "JSONKey": "id"
      }
    ],
    "Relationships": {
      "reviews": {
        "IdentityColumn": "reviews_id",
        "Fields": [
          {
            "ColumnAlias": "reviews_id",
            "JSONKey": "id"
          },
          {
            "ColumnAlias": "reviews_reviewRating",
            "JSONKey": "reviewRating"
          }
        ],
        "Relationships": {
          "user": {
            "IdentityColumn": "user_id",
            "Fields": [
              {
                "ColumnAlias": "user_id",
                "JSONKey": "id"
              },
              {
                "ColumnAlias": "user_reviewRating",
                "JSONKey": "reviewRating"
              }
            ],
            "Relationships": {},
            "Type": 1
          }
        },
        "Type": 1
      }
    },
    "Type": 0
  },
  "message": "SQL query for owner rollthecloudinc/hedge-objects successfully generated."
}

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT, -- Auto-incrementing Primary Key
    name VARCHAR(100) NOT NULL,
    username VARCHAR(100) UNIQUE,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE artifacts (
    id INT PRIMARY KEY AUTO_INCREMENT, -- Auto-incrementing Primary Key
    users_id INT,                      -- Foreign key to users.id
    name VARCHAR(255) NOT NULL,
    description TEXT,
    FOREIGN KEY (users_id) REFERENCES users(id)
);

CREATE TABLE reviews (
    id INT PRIMARY KEY AUTO_INCREMENT, -- Auto-incrementing Primary Key
    artifacts_id INT,                  -- Foreign key to artifacts.id
    users_id INT,                      -- Foreign key to users.id
    feedback TEXT,
    rating INT,
    review_date DATE,
    FOREIGN KEY (artifacts_id) REFERENCES artifacts(id),
    FOREIGN KEY (users_id) REFERENCES users(id)
);

-- Insert into users table
INSERT INTO users (name, username, email) VALUES
('Alice Johnson', 'alice_j', '[email protected]'), -- id=1
('Bob Smith', 'bob_s', '[email protected]'),       -- id=2
('Charlie Brown', 'charlie_b', '[email protected]'); -- id=3

-- Insert into artifacts table
INSERT INTO artifacts (users_id, name, description) VALUES
(1, 'Ancient Vase', 'A relic from the 3rd century.'), -- id=1, Owned by Alice (id 1)
(2, 'Digital Art Piece', 'A unique NFT graphic.'), -- id=2, Owned by Bob (id 2)
(1, 'Old Map', 'A detailed chart of the coast.'); -- id=3, Owned by Alice (id 1)

-- Insert into reviews table
-- Assuming artifact IDs 1, 2, 3 were assigned above.
INSERT INTO reviews (artifacts_id, users_id, feedback, rating, review_date) VALUES
(1, 1, 'Absolutely stunning artifact.', 5, '2025-10-01'),
(2, 2, 'Good concept, needs refinement.', 3, '2025-10-05'),
(1, 3, 'A must-see item.', 5, '2025-10-10'),
(3, 1, 'Very interesting historical document.', 4, '2025-10-15');