Skip to content

Custom condition #94

Closed
Closed
Custom condition#94
Labels
bugcode added

Description

@czkody
czkody
opened on Apr 2, 2013

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

Activity

  1. dresende commented on Apr 2, 2013

    @dresende
    dresende
    on Apr 2, 2013
    Owner

    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.

  2. dresende commented on Apr 2, 2013

    @dresende
    dresende
    on Apr 2, 2013
    Owner

    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?

  3. czkody commented on Apr 2, 2013

    @czkody
    czkody
    on Apr 2, 2013
    Author

    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.

  4. dresende commented on Apr 2, 2013

    @dresende
    dresende
    on Apr 2, 2013
    Owner

    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) {
    // ...
});

  5. czkody commented on Apr 2, 2013

    @czkody
    czkody
    on Apr 2, 2013
    Author

    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.

  6. dresende
    added a commit that references this issue on Apr 3, 2013

    Adds hasMany hasAcessor conditional to ChainFind (#94)

    d371e07

  7. dresende commented on Apr 3, 2013

    @dresende
    dresende
    on Apr 3, 2013
    Owner

    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) {
    // ...
});

  8. czkody commented on Apr 3, 2013

    @czkody
    czkody
    on Apr 3, 2013
    Author

    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

  9. dresende commented on Apr 3, 2013

    @dresende
    dresende
    on Apr 3, 2013
    Owner

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

  10. dresende
    added 2 commits that reference this issue on Apr 3, 2013

    Adds support for hasMany hasAccessor conditional to ChainFind.count() (…

    481a984

    Adds hasMany hasAccessor conditional to ChainFind.remove() (#94)

    658c6af

  11. czkody commented on Apr 3, 2013

    @czkody
    czkody
    on Apr 3, 2013
    Author

    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.

  12. czkody commented on Apr 3, 2013

    @czkody
    czkody
    on Apr 3, 2013
    Author

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

  13. czkody
    closed this as completedon Apr 3, 2013

  14. czkody commented on Apr 16, 2013

    @czkody
    czkody
    on Apr 16, 2013
    Author

    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

  15. czkody
    reopened this on Apr 16, 2013

  16. dresende commented on Apr 16, 2013

    @dresende
    dresende
    on Apr 16, 2013
    Owner

    Please try the latest git version if you can.

  17. czkody commented on Apr 16, 2013

    @czkody
    czkody
    on Apr 16, 2013
    Author

    Error message from last git version is:

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

  18. dresende commented on Apr 17, 2013

    @dresende
    dresende
    on Apr 17, 2013
    Owner

    Could you post the query you're trying to use?

  19. czkody commented on Apr 17, 2013

    @czkody
    czkody
    on Apr 17, 2013
    Author

    Following code is part of CategoryController of above mentioned model.

        var category_id = Number(req.params['category']); // 812
    var categoryInstance = Category(category_id);
    var productCount = categoryInstance.getProducts({});
    productCount.hasPropertyValue(4564)
    productCount.count(function (err, count) {
        if (count > 0) {
            ....
        }
    }

    Produced SQL query should be something like:

    SELECT 
   count(*) as count
FROM 
    `product` `t1` 
    JOIN `category_product` `t2` ON `t2`.`product_id` = `t1`.`id` 
WHERE 
    (`t2`.`category_id` = 812)  
    AND EXISTS(
        SELECT * FROM `product_property_value` WHERE `product_id` = `t1`.`id` AND `property_value_id` = 4564
    )

  20. dresende commented on Apr 17, 2013

    @dresende
    dresende
    on Apr 17, 2013
    Owner

    I can't test it right now but could you try and remove the {} from categoryInstance.getProducts() ? I'm not sure but if that solves it we found the bug.

  21. dresende commented on Apr 17, 2013

    @dresende
    dresende
    on Apr 17, 2013
    Owner

    I think I've replicated your problem. I'm going to investigate.

  22. dresende
    added 2 commits that reference this issue on Apr 17, 2013

    Fixes bug on EXISTS query part (dresende/node-orm2#94)

    59fc476

    Updates sql-query dependency to 0.0.19 (#94)

    ce5a233

  23. dresende commented on Apr 17, 2013

    @dresende
    dresende
    on Apr 17, 2013
    Owner

    Update your git version, run npm install (to update sql-query dependency) and try again. If this fixes it, I'm going to release a new version this week.

  24. czkody commented on Apr 18, 2013

    @czkody
    czkody
    on Apr 18, 2013
    Author

    Tested and works fine. Thank you very much.

  25. czkody
    closed this as completedon Apr 18, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugcode added

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions