Check product is purchased , to unlock next one!

Does this work?

Order::where('user_id',$userId)->where('product_id',$productId)->exists();

@Cronix it depend by required_product_id what is $productId?

Lol what do you mean? I copied your query from stackoverflow and adjusted it... I assume YOU know where it comes from. I didn't add/change any variables. I just used exists() instead of count().

Your original was

Order::where('user_id',$userId)->where('product_id',$productId)->count()

if (Order::where('user_id',$userId)->where('product_id',$productId)->exists()) {
    // buyable
} else {
    // not buyable
}

So listen, i have required_product_id in my product table and i want to use it in if condition to check is that is purchased and if not.. See that page: http://iplexmc.eu/p/1 (It will ask u for username so enter it and see what im tryna make) Thx @Cronix

bump

If I undertands you correctly something like this should work. The first part selects all products that does not require any previous purchase and the second only selects the ones that exists in the oreders table for the user.

$purchasableItems = Db::select(
  'SELECT p.*
  FROM products p 
  WHERE required_product_id IS NULL 
  UNION ALL
  SELECT p.* 
  FROM products p, orders o 
  WHERE p.required_products_id = o.product_id 
  AND o.user_id =' .  $user)->get();

it give me error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8 (SQL: SELECT p.* FROM products p WHERE required_product IS NULL UNION ALL SELECT p.* FROM products p, orders o WHERE p.required_product = o.product_id AND o.user_id =)

Try $user->id instead of $user

@Tray2 Yeah, it work , thx but now how i can check in blade is it buyable or not, and too i want that like site first product will be with class active in html and button will save "Buy It" and another products too be displayed but it will be with class disabled and button "Purchase previous". And when user buy unlocked rank to disapear!

here is example code:

        $products = $category->products()->where('onlyOnce', 'Y')->pluck('id')->toArray();
        $orderedProducts = $user->orders()->pluck('product_id')->toArray();
        $missProducts= array_intersect($orderedProducts, $products);

        $extras = $category->products()->where('onlyOnce', 'Y')->doesntHave('orders')->get();

You don't have to since the ones that the user can't buy will not be included in the result.

If you wan't to display them then you need to include them and set a buyable column in the sql to 'N' and then you can filter on that.

SELECT p.*, 'N' buyable FROM products WERE and so on.

So in the first query you list all buyable items in the second you list all then ones that the user unlocked and then you add a thirds select where you select all the locked ones.

Oh, I did it! Thanks for a lot of support dude! You'r awesome!

@Tray2 One more question if i want to make this product buyable only once, i have

table onlyOnce they must Y - Yes/N - No and i want when user purchase that to remove from included list

Use the same technique as in the second query but use the only_once table to see if the product is there or not and has and order by the user

yeah but i want to explain in in same thing with purchasableItems @Tray2

Use another union in the SQL to add those items

And you can add a placeholder column for that something like this.

$purchasableItems = Db::select(
  "SELECT p.*, 'purchaseable' product_type
  FROM products p 
  WHERE required_product_id IS NULL 
  UNION ALL
  SELECT p.* , 'leveling' product_type
  FROM products p, orders o 
  WHERE p.required_products_id = o.product_id 
  AND o.user_id =' .  $user .
  'UNION ALL
  SELECT p.* , 'onlyonce' product_type
  FROM products p, orders o, only_once on 
  WHERE p.required_products_id = o.product_id
  AND p.id = on.product_id 
  AND p.id NOT IN (SELECT 'x' FROM orders WHERE o.product_id = p.id and o.user_id =' . $user->id . '))->get();

Where is placeholder column?

Give structure of my table how it will look like?

It's the product_type column that is not in your tables but rather added dynamically from the query,

Hm.. Im not sure i can understand that part @Tray2 can you explain more details? Where i must create column cuz if not in my tables where it must be? :D

@Tray2 I must do it like this

|id|name|desc|bla|bla|product_type
1 name desc........HERE WHAT ?

You don't have to have product_type in the table it's added with SQL in each statement in the union.

Just the same way you can select 'Charles' name from products. It will give you a column named name with the value of charles for each row in your table

@Tray2 hhttp://prntscr.com/k6hsut This is my table

This is my changed code

        $extras = DB::select(

            'SELECT p.*
  FROM products p 
  WHERE required_product IS NULL 
  UNION ALL
  SELECT p.* 
  FROM products p, orders o 
  WHERE p.required_product = o.product_id 
  AND o.user_id =' .  $user->id .  'UNION ALL
  SELECT p.* , "onlyOnce" product_type
  FROM products p, orders o, only_once on 
  WHERE p.required_products = o.product_id
  AND p.id = on.product_id 
  AND p.id NOT IN (SELECT "x" FROM orders WHERE o.product_id = p.id and o.user_id ='.$user->id);

What im doing wrong cuz it give me error?

My bad was mixing exists and not in.

Change 'x' to product_id

You should also give orders an alias in the subquery i.e. orders so and 'x' then becomes so.product_id

@Tray2 i change it to this way:

        $extras = DB::select(

            'SELECT p.*
    FROM products p 
    WHERE required_product IS NULL 
    UNION ALL
SELECT p.* 
FROM products p, orders o 
WHERE p.required_product = o.product_id 
AND o.user_id =' .  $user->id .  ' UNION ALL
SELECT p.* , "onlyOnce" product_type
FROM products p, orders o, only_once 
WHERE p.required_product = o.product_id
AND p.id = only_once.product_id 
AND p.id NOT IN (SELECT product_id FROM orders WHERE o.product_id = p.id and o.user_id ='.$user->id.')'
        );

Still return that:

http://38f2c4c9.ngrok.io/category/rangove

I give u my host link to check what it come from

SELECT p.*, "Y", onlyOnce
  FROM products p 
  WHERE required_product IS NULL 
  UNION ALL
  SELECT p.*, "Y", onlyOnce
  FROM products p, orders o 
  WHERE p.required_product = o.product_id 
  AND o.user_id = 1 UNION ALL
SELECT p.* , "onlyOnce","Y"
FROM products p, orders o, only_once once
WHERE p.required_product = o.product_id
AND p.id = once.product_id
AND p.id NOT IN (SELECT product_id FROM orders WHERE o.product_id = p.id and o.user_id=1) 

I change it to that cuz my colum onlyOnce in products must be Y/N and i make another table called only_once with user and product_id but i have problem it display same thing like before? :D if i add in orders it will unlock next and not hide current ;( i want to make it purchasable only once

I hope tommorow u will help mu cuz i very need that option to added in my site

Show me your migrations of all the tables involved and the data all the different data in them and a mock of the result you want.

Products: http://prntscr.com/k6mbx4, Orders: http://prntscr.com/k6mbzv only_once: http://prntscr.com/k6mc1p

So when user buy the rank current purchased rank to remove from list and display anotherone but if onlyOnce is set to N current purchased rank to not remove from list!

@Tray2

I would use this database structure instead.

The products table

        Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('description');
            $table->unsignedInteger('recommended');
            $table->integer('price');
            $table->string('image');
            $table->unsignedInteger('reuired_product_id')->nullable();
            $table->unsignedInteger('max_buys'); 
            $table->integer('discount')->nullable();
            $table->timestamps();
        });

If max_buys = 0 you can buy as many as you desire and as many times (unlimited).

The orders table is good as it is.

I would throw out the only_once table since you can see in the orders table if the user has bought the product or not.

Then the price should always be an integer since calculating with floats are unreliable. Then you can adapt the SQL I already supplied to you to fit this new table structure.

Ok, but i have problem with supplied can you give me query? Cuz i have table and union is might difficult to understand to me @Tray2

It's really not that dificult.

Check this link https://www.w3schools.com/sql/sql_union.asp

They have some pretty good SQL tutorials also which I recommend reading.