Custom condition

[czkody]

Hello dresende,

thank you very much for your work on this project, it helps me a lot. I'am using your orm for accessing mysql database with about 30M records and it works fine. So now I'm looking for some way to add custom condition to where clause.

Here is my SQL schema:

CREATE TABLE `category` (
  `id` int(11) NOT NULL,
  `name` varchar(1024) COLLATE utf8_czech_ci NOT NULL,
  `url` varchar(200) COLLATE utf8_czech_ci NOT NULL,
  `leaf` tinyint(4) NOT NULL DEFAULT '0',
  `number_of_products` int(11) NOT NULL DEFAULT '0',
  `path` varchar(800) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `category_tree` (
  `predecessor_id` int(11) NOT NULL,
  `successor_id` int(11) NOT NULL,
  `distance` int(11) NOT NULL,
  PRIMARY KEY (`predecessor_id`,`successor_id`),
  KEY `FK_category_tree_successor` (`successor_id`),
  CONSTRAINT `FK_category_tree_predecessor` FOREIGN KEY (`predecessor_id`) REFERENCES `category` (`id`),
  CONSTRAINT `FK_category_tree_successor` FOREIGN KEY (`successor_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `product` (
  `id` int(11) NOT NULL,
  `name` varchar(512) COLLATE utf8_czech_ci NOT NULL,
  `description` text COLLATE utf8_czech_ci,
  `rating` int(11) DEFAULT NULL,
  `ean` varchar(20) COLLATE utf8_czech_ci DEFAULT NULL,
  `price` decimal(14,2) DEFAULT NULL,
  `image_url` varchar(1024) COLLATE utf8_czech_ci DEFAULT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `price` (`price`),
  KEY `ean` (`ean`),
  KEY `name` (`name`(180)),
  KEY `date` (`date`),
  CONSTRAINT `FK_category_product` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `category_product` (
  `category_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`,`product_id`),
  KEY `product` (`product_id`),
  CONSTRAINT `FK_category_product_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`),
  CONSTRAINT `FK_category_product_product` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `property` (
  `id` int(11) NOT NULL,
  `name` varchar(200) COLLATE utf8_czech_ci NOT NULL,
  `description` text COLLATE utf8_czech_ci,
  `unit` varchar(512) COLLATE utf8_czech_ci DEFAULT NULL,  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `property_value` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(512) COLLATE utf8_czech_ci NOT NULL,
  `property_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `property_id_value` (`property_id`,`value`(200))
) ENGINE=InnoDB AUTO_INCREMENT=525900 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `product_property_value` (
  `product_id` int(11) NOT NULL,
  `property_value_id`  int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`property_value_id`),
  KEY `property_value` (`property_value_id`),
  CONSTRAINT `FK_product_property_value_product` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`),
  CONSTRAINT `FK_product_property_value_property_value` FOREIGN KEY (`property_value_id`) REFERENCES `property_value` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE `category_property` (
  `category_id` int(11) NOT NULL,
  `property_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`,`property_id`),
  KEY `property_id` (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

I'm getting products for category by:

...
Category(category_id).getProducts( ...
...

SQL statement looks like this:

SELECT 
    `t1`.`id`, `t1`.`name`, `t1`.`ean`, `t1`.`price`, `t1`.`date`, `t1`.`description`, `t2`.* 
FROM 
    `product` `t1` 
    JOIN `category_product` `t2` ON `t2`.`product_id` = `t1`.`id` 
WHERE 
    (`t2`.`category_id` = 813) 
ORDER BY 
    `t1`.`name` ASC 
LIMIT 250 OFFSET 1250 

But now I need to add filtering by property values which means adding another condition:

SELECT 
    `t1`.`id`, `t1`.`name`, `t1`.`ean`, `t1`.`price`, `t1`.`date`, `t1`.`description`, `t2`.* 
FROM 
    `product` `t1` 
    JOIN `category_product` `t2` ON `t2`.`product_id` = `t1`.`id` 
WHERE 
    (`t2`.`category_id` = 813)  
    AND EXISTS(
        SELECT * FROM `product_property_value` WHERE `product_id` = `t1`.`id` AND `property_value_id` = 4564
    )
ORDER BY 
    `t1`.`name` ASC 
LIMIT 250 OFFSET 1250 

So is there any correct way to do that?

Thank you very much,
kody

[dresende]

Can you post the SCHEMA and some dummy data so I can try it locally and see how I can create a simple and clean syntax to the query?

Maybe a link on droplr or dropbox or something.. no need to have much data, just some in every table at least to run that last query a see one result.

[dresende]

I copied your schema and added 2 rows on every item from the tables in your last query (changing your ids in WHERE condition). I'm still trying to figure out the relationship between the tables. Is this your hierarchy?

category -> product -> property_value <- property

If so, you're trying to fetch products that belong to a specific category, but only those which have (at least) a specific property_value. Am I right?

[czkody]

Yes you're right. I know that i can filter records in nodejs, but it's too slow. Only way for me is do it at SQL. I'm creating subset of my schema for you.

[dresende]

It's late here, I have to dinner now, but I'm going to think about this after. I believe the best approach is using my ChainFind class. Something like:

Category(813).getProducts()
             .only('id', 'ean')
             .limit(250)
             .offset(1250)
             /* something here like .withProperty(4564)*/
.run(function (err, products) {
    // ...
});

[czkody]

Bon appetite :), I have just finished subset of schema. You can download it here: http://download.kodytek.net/dump_orm2013-04-02.sql.gz . I am really curious on solution, in SQL I can solve it in few seconds, but at application level i'm a begginer. Thank you very much.

[dresende]

Ok, I made some changes that I think will work to solve this problem. You'll have to use the git version and not npm version for now. If you don't know how to do it just tell me.

I assume you have somewhere in your code something like:

Product.hasMany("property_value", PropertyValue);

Because of this, when you have a product, you have access to a_product.getPropertyValue(). There's also another accessible method - a_product.hasPropertyValue(). I picked this last one (it's called has accessor) and added it to ChainFind. So the final code is similar to my previous comment mockup:

Category(813).getProducts()
             .only('id', 'ean', 'price', /* ... */)
             .hasPropertyValue(4564) // <-- the new filter method
             .limit(250)
             .offset(1250)
.run(function (err, products) {
    // ...
});

[czkody]

Hi,
I've just tested it and it works fine. I still do not completely understand how you did it, but it works exactly as you said.

Only little problem remains, it doesn't work for .count().

Thank you very much,
kody

[dresende]

You mean, instead of .run() use .count()? I'm going to check it out.

[czkody]

Yes you're right. I'm using server side paging so I need to get size of the subset and then fetch only requested page of data.

[czkody]

Tested and works well. Thank you very much man, you have my gratitude.

[czkody]

Hi Diogo,
after installation of version 2.0.8 I get following error when hasAccessor of hasMany association is used.

TypeError: Object # has no method 'concat' at Object.SelectQuery.proto.whereExists (F:\app\products-api\node_modules\sql-query\lib\Select.js:125:71) at Driver.count (F:\app\products-api\node_modules\orm\lib\Drivers\DML\mysql.js:154:6) at Object.ChainFind.chain.count (F:\app\products-api\node_modules\orm\lib\ChainFind.js:35:16) ... ...

Kody