Product Table - Nop DB

Posted: July 16, 2019 at 2:37 PM Quote #242558
I need to figure out what the relation is between the product table and the store table (if there is one). I need to write a sql script to pull some info based on store id (ie. SELECT * FROM PRODUCTS WHERE storeID = '11'). Is there a way to do a join that I'm just not seeing?
ANY help is GREATLY appreciated.

Cheers,

Brad
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: July 16, 2019 at 3:31 PM Quote #242561
There is another table that links the relationship
Have a look at StoreMapping Table

Id  EntityId  EntityName    StoreId
1  17         Product                          2
5  24         MessageTemplate        2
6  38         Product                          1
2  44         MessageTemplate        1
4  47         MessageTemplate        2
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
www.SelectSystems.com.au
Select Systems International is a computer systems technology solution developer and integration service provider.
Posted: July 16, 2019 at 6:58 PM Quote #242564
Thank you very much for this info, I'll see what I can work out and update shortly!

Cheers!

Brad
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: July 17, 2019 at 3:59 AM Quote #242586
To add on to the point, StoreMapping is a generic table used by several entities. That's why you see the entity name as one of the fields. :)


This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Lam Woon Cherk
https://www.pronopcommerce.com

Looking to double your store's sale? Check out this plugin: http://bit.ly/2YVP8GT
(And no, it's not about putting more marketing money to get more traffics)

Sign up to my mailing list for tons of tips on nopCommerce: http://bit.ly/2fg5fMy
Posted: July 17, 2019 at 8:10 AM Quote #242599
SAMPLE FOR THIS QUERY IS

      
SELECT * FROM product p WHERE  (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId=' 1 '))
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Pls up-vote the answer, if it helps you!

Thank's,
Sangeet Shah
====================================================
NopAdvance - nopCommerce Solution Partner
Website: https://nopadvance.com
Posted: July 17, 2019 at 8:28 AM Quote #242601
Sangeet,

Thank you so very much, this helped tremendously. The issue I'm having now, is that our previous developer added a new table called 'TierPrice' (which actually has the correct pricing information per product). So I'm not getting the correct pricing without bringing in this table as well. Admittedly I'm not a db guy lol, Here is what the 'TierPrice' table looks like:

TierPrice: ( https://imgur.com/YXHbtkN -- Image isn't showing below)


Is there a way I can pull the 'Price' from 'TierPrice' Into your query?
(ie: SELECT * FROM product p WHERE  (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId='11'))   )

Any help is GREATLY appreciated.

Cheers,

Brad
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: July 18, 2019 at 1:19 AM Quote #242634
b.aytes wrote:
Sangeet,

Thank you so very much, this helped tremendously. The issue I'm having now, is that our previous developer added a new table called 'TierPrice' (which actually has the correct pricing information per product). So I'm not getting the correct pricing without bringing in this table as well. Admittedly I'm not a db guy lol, Here is what the 'TierPrice' table looks like:

TierPrice: ( https://imgur.com/YXHbtkN -- Image isn't showing below)


Is there a way I can pull the 'Price' from 'TierPrice' Into your query?
(ie: SELECT * FROM product p WHERE  (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId='11'))   )

Any help is GREATLY appreciated.

Cheers,

Brad



try with below query to get price into last column of result query

DECLARE @CurrentDate as DATETIME =SYSDATETIME()
DECLARE @CustomerRoleId int
SET @CustomerRoleId=1 -- this is your current login customer role id

SELECT * FROM product p
left  join TierPrice t on p.Id = t.ProductId
and ((T.StartDateTimeUtc <= @CurrentDate OR T.StartDateTimeUtc IS NULL)  AND (@CurrentDate  <= T.EndDateTimeUtc  OR T.EndDateTimeUtc IS NULL))
AND (t.CustomerRoleId =0 or t.CustomerRoleId = @CustomerRoleId OR t.CustomerRoleId is NULL )
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Pls up-vote the answer, if it helps you!

Thank's,
Sangeet Shah
====================================================
NopAdvance - nopCommerce Solution Partner
Website: https://nopadvance.com
Premium support services
  • Dedicated premium support services provided by core developers are intended for persons who run mission critical websites, work on projects with tight deadlines, or want to get dedicated support.
Professional services
  • Want to open a new store? Want to take your store to the next level? Need a custom extension? We can customize nopCommerce to fit your store perfectly. Request a quote to get started.
eCommerce CONFERENCE 2019
Learn more