Debugging in Node.js under Windows

In order to debug a node.js server, you simply have to have installed node.js together with node-inspector debugger package.
To install node-inspector, type in the command prompt the following line:

npm install node-inspector

Suppose that after installation you have the following directory structure:

C:\Program Files\nodejs
+---node_modules
|   +---node-inspector
|   |   +---bin
|   |   |   +---inspector.js

In command prompt, go to the root folder of node.js, and type the following command:

C:\Program Files\nodejs>node node_modules\node-inspector\bin\inspector.js


Now, open another command prompt and go to the root folder of your node.js project, and type the following command:

C:\Program Files\nodejs\Workspace\MyProject\src>node --debug index.js


Everything is set and done now. To start debugging, first in a browser (e.g. Google Chrome) go to http://localhost:8080/debug?port=5858 and in anoter tab go to your project’s address (e.g. my node.js application listens on port 4000, so I will navigate to http://localhost:4000)

In the first tab you can navigate through your project’s source files, set breakpoints, run step by step, view the call stack etc. just like you would normally debug a client js file. In the second tab you can navingate through your application.

Have fun!

OOP in JavaScript-Prototypal Inheritance

Is JavaScript an Object-Oriented Programming language? Does JavaScript support inheritance? Believe it or not, these are some tough questions that stir up endless discussions.

As in W3Schools.org, “JavaScript is THE scripting language of the Web” On Wikipedia, JavaScript is characterized as “a prototype-based scripting language that is dynamic, weakly typed, general purpose programming language and has first-class functions. It is a multi-paradigm language, supporting object-oriented, imperative, and functional programming styles”.

However, form my point of view, if JavaScript supports object-oriented programming style, doesn’t mean that is an object-oriented programming language, at least not in the way that Java/C#/PHP 5+ is! I think that “JavaScript is an object-based language” is more realistic and closer to truth. However, one of the main characteristics of an OOP language is inheritance, which by the way is supported in JavaScript through prototyping.

Let’s consider a “class constructor” called Human. Also consider Human as the base class:

function Human() {};

Since Human is a Function object (everything in JS is an object, even the Function!), it can have properties like “prototype”. In this moment, the prototype property of Human has an automated property called “constructor” which points to itself (to Human function).
If you try to debug this line of code with the Developer Tools from Chrome, you will have the following results:

Property ===> Expected result
DEBUG: Human.prototype ===> Human {}
DEBUG: Human.hasOwnProperty(‘prototype’) ===> true
DEBUG: Human.prototype.constructor ===> Human ()
DEBUG: Human.hasOwnProperty(‘constructor’) ===> false
DEBUG: Human.prototype.hasOwnProperty(‘constructor’) ===> true
DEBUG: typeof Human ===> ‘function’
DEBUG: Human instanceof Function ===> true
DEBUG: Object.getPrototypeOf(Human.prototype) ===> Object {}
DEBUG: Object.getPrototypeOf(Human.prototype) === Object.prototype ===> true

When an object is constructed, that object gets an implicit reference to the value of the constructor’s prototype property, which is an object. “Implicit reference” means an internal thing, not some property of the object. The implicit reference is hidden away, but it can be gotten with Object.getPrototypeOf(). E.g. When we write function Human() {}, Human has a new object constructed called prototype which “gets an implicit reference to the value of the constructor’s prototype property (Object.prototype)”. The “constructor’s prototype property” is finally an object (the base of all the things in JavaScript).

Property ===> Expected result
DEBUG: Object.getPrototypeOf(Human.prototype).constructor === Object ===> true
DEBUG: Object.prototype ===> Object {}

Next, consider the following line of code:

Human.prototype = {
  name : 'John'
};

After this line of code, the prototype will become a simple object, and because of poor JavaScript design, the prototype’s constructor property will point to Object (). That’s because the constructor property is no longer part of the Human.prototype, but rather of Object.prototype (if the property isn’t found in the prototype object, it is looked for in the prototype object’s prototype).

Property ===> Expected result
DEBUG: Human.prototype ===> Object { name : ‘John’}
DEBUG: Human.prototype.constructor ===> Object ()
DEBUG: Human.prototype.hasOwnProperty(‘constructor’) ===> false

Human.prototype.color = 'white';
Human.prototype.getName = function() {
  return this.name;
};
Human.prototype.getColor = function() {
  return this.color;
};

After these lines of code, the prototype will become Object { name : ‘John’, color : ‘white’, getName : function() { return this.name }, getColor : function() { return this.color } }
And now for the “object instantiation”:

var h = new Human();

Also the poor design of JavaScript is further reflected when instantiating the object h. That is, the contructor is not taken from Human.prototype, because as previously mentioned, the prototype of Human is a simple object with no automated constructor property and that’s why the constructor is taken from one level up which is from Object.prototype.

Property ===> Expected result
DEBUG: h.constructor ===> Object ()
DEBUG: Object.getPrototypeOf(h) ===> Object {
name : ‘John’,
color : ‘white’,
getName : function() {
return this.name;
},
getColor : function() {
return this.color;
} }
DEBUG: typeof Object.getPrototypeOf(h) ===> ‘object’
DEBUG: typeof h ===> ‘object’

To “patch” JavaScript’s poor design, we will create the constructor property which points to itself (to Human)

Human.prototype.constructor = Human;

Property ===> Expected result
DEBUG: Human.prototype ===> Human {
name : ‘John’,
color : ‘white’,
getName : function() {
return this.name;
}, getColor : function() {
return this.color;
}
}
DEBUG: Human.prototype.constructor ===> Human ()
DEBUG: Human.prototype.hasOwnProperty(‘constructor’) ===> true
DEBUG: h.constructor ===> Human ()
DEBUG: Object.getPrototypeOf(h) ===> Human {
name : ‘John’,
color : ‘white’,
getName : function() {
return this.name;
},
getColor : function() {
return this.color;
}
}

And now for the child class, which in our example will be called SuperHuman:

function SuperHuman(superpower) {
  this.superpower = superpower;
};
 
SuperHuman.prototype = new Human(); // inherit from Human
 
SuperHuman.prototype.getSuperpower = function() {
  return this.superpower;
};
 
var sh = new SuperHuman('laser');
 
SuperHuman.prototype.constructor = SuperHuman;

If you have the curiosity to debug the code and check the state of the properties after each line of code, you will see that SuperHuman will behave in the exact same way as Human did – that’s because SuperHuman inerits from Human and therewith all the design problems that we encountered before.
This is in a nutshell the prototypal inheritance in JavaScript and as you can see the power of it can be unleashed only if we simulate it correctly and correct the poor design of JavaScript by explicitly setting up the constructor (e.g. SuperHuman.prototype.constructor = SuperHuman)!

HTML5 Websockets, Node.js And Socket.io On Windows

Assuming that you already Googled around for the terms “HTML5 WebSockets”, “node.js” and “socket.io” and you came to the point where you wanted to actually test it a bit, you can follow the steps below so that you can create the magic with your own hands.

“The server side”
What do you need to do in order to run your first WebSockets server on Windows:
- go to http://nodejs.org and download the “Windows Installer”
- install it
- in command prompt, go to C:\Program Files\nodejs and run the following command:

npm install socket.io

- under C:\Program Files\nodejs create a new file named server.js with the following content:

var http = require('http');
var io = require('socket.io');
 
//WebSocket
var server = http.createServer(function(request, response) {
    response.writeHead(200, {'Content-Type': 'text/html'});
    response.end('WebSocket server is up and running!');
}).listen(33334);
 
//Socket.IO
var socket = io.listen(server).set('log level', 1);
 
//Listeners for intercepting client messages
socket.on('connection', function(client) {
    client.on('message', function(data) {
        console.log('Message from client: ', data);
    });
    client.on('customMessage', function (data) {
        console.log('Custom message from client: ', data);
 
        var now = new Date().getTime();
 
        //Emits back to the client a message
        client.emit('customMessageResponse', data + '->' + now);
 
        //Broadcasts to all the clients a message
        client.broadcast.emit('customMessageResponse', data + '(broadcasted)->' + now);
    });
    client.on('disconnect', function(){
        console.log('Client disconnected');
    });
});

- in command prompt run the following command:

node server.js

- voila! the server is up and running
So far so good with the server side. But what about the client side?

“The client side”
- download a test HTML file from here http://goo.gl/9ul1O
- allow the file to be served by your favorite web server (IIS, Apache etc.)
Now for the tests!

“The magic”
- go to http://localhost/downloaded_html_file.html (for demonstration purposes, I will open it in 3 separate browser instances)
- almost instantly you should see the message “Client connected to the Server!”
- type something in the first input box, click on “Send Message!” and you should see the message in the WebSockets server log window, just like in the image below:

- now let’s test the broadcast functionality of the WebSocket – type something in the second input box, click on “Send CustomMessage!” and you should not only see the message in the WebSockets server log window but also in the 3 separate browser instances:

- if you’re curious to inspect the HTTP traffic (through Fiddler for example), you can see that in order to establish a WebSocket connection, the HTTP connection must be upgraded to a WebSocket connection (HTTP/1.1 101 Switching Protocols)

- unfortunately Fiddler logs the client-server communication in a way that’s a bit messy

PHP multithreading through .NET interop

Is PHP supporting multithreading? The answer is NO, if you are excluding the interop mechanism.
First of all, let me tell you a story about Threading. A CPU (computer’s processor) can have one or multiple cores. Each core can have one or multiple processes spawned by applications which in turn can be forked with child processes. Each process / child process can have one or multiple threads which are “the smallest unit of processing that can be scheduled by an operating system” (Wikipedia). Further down, Microsoft comes with a concept of task, which is included in the latest version of the .NET Framework – Parallel Framework (PFX).

For Unix we have http://www.php.net/manual/en/intro.posix.php andhttp://www.php.net/manual/en/intro.pcntl.php which are a set of complementary libraries that allows process manipulation operations. Let me repeat that: “process manipulation operations”… since there is nothing mentioned about threading, you can simply put it this way: PHP doesn’t natively support threading in Unix through the libraries mentioned above, instead it supports process forking. As you may already noticed, I used the term of “natively” because you can achive multithreading by interoping with languages like Java, C / C++ or even .NET through Mono. If you may wonder what are the advantages of using threads over processes in general, read thishttp://www.programmerinterview.com/index.php/operating-systems/thread-vs-process/; in particular, each language can have it’s own mechanisms of dealing with threads. A nice article on “multithreading” in PHP can be found on my comrade’s blog Tudor a.k.a. motaneluhttp://blog.motane.lu/2009/01/02/multithreading-in-php/.

Since posix and pcntl libraries are not supported on Windows, I’m going to assume that some of you who are running PHP under Apache / IIS on Windows will still want at some point to multithread the application. Have no fear, .NET is here! If you are familiar with C#, VB.NET or maybe F#, you can easily take advantage of the interoperability between PHP and .NET through the DOTNET classhttp://www.php.net/manual/en/class.dotnet.php and with it of all the goodies that the .NET Framework 4.0 is made of: PLINQ, Parallel class, task parallelism, concurrent collections and other. Make no mistake, as I already mentioned, the interop mechanism can be also used from Unix in pair with some languages, but since I primarily am a .NET programmer, I’m going to explain this through C# code.

I will assume that you have installed at least Visual C# 2010 Express Edition. First create a new Class Library project: File -> New Project -> Class Library. We’re going to call it MultithreadedDotNetClass. The purpose of this library is to add random numbers from multiple threads, avoiding duplicates. I will attach the source code here http://goo.gl/1so4v

In order to install the new assembly in the GAC, so that we could use it with the DOTNET PHP class, we need to sign it with a strong name. You can either do this from the IDE or with the SN tool from the .NET SDK. I will do this from the IDE, by going to Project -> MultithreadedDotNetClass Properties… -> Signing -> Sign the assembly.

Before building the library, I will make this assembly visible to COM components by setting the [assembly: ComVisible()] attribute to true (by default is false). Now you can finally build it.
Now let’s install our assembly in the GAC. Open a new command prompt, go to the location where gacutil is installed and type the following:

gacutil -I "C:\Path\To\The\Library\MultithreadedDotNetClass.dll"

For the test create a new PHP file, test.php, with the following content, replacing the Version / PublicKeyToken with your own:

$multithreadedDotNetObject = new DOTNET('MultithreadedDotNetClass, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b9137eaca16ad247', 'MultithreadedDotNetClass.Program');
$multithreadedDotNetObject->DoWork();

If you have trouble in finding the Version / PublicKeyToken for the MultithreadedDotNetClass assembly, type this in the command prompt:

gacutil /lr MultithreadedDotNetClass

The result will be the Strong Name of the assembly:

After running our test, the output is as expected:

Have fun!

PHP dream development environment

Have you ever dreamed to have a set of cross-platform PHP development tools that not only would make your life easier when programming from both Windows and Linux / Mac OS X but also would have a self sufficient full-featured environment to build a new Facebook from scratch ? Sounds like a swiss army knife for PHP developers? Well, now you have it! I’m not going to describe in detail the features of each tool, but rather to summarize them.

Eclipse PDT (PHP Development Tools) + Xdebug is a complete IDE for PHP which has a nice add-ons market for extra features you might need for coding. The only drawback is that it tends to eat lot of resources and slows your computer down, but nowadays the RAM memory is very cheap, so you’re good to go.

Navicat for MySQL is a MySQL IDE that simply is the best choice over phpMyAdmin and MySQL Workbench. Althought the Standard Edition (for reporting capabilities consider buying the Enterprise Edition) costs $129, it simply outcomes the price. The only important feature which I didn’t found in this tool is the tracing capability, but Jet Profiler for MySQL does this for you.

Jet Profiler for MySQL is a “real-time query performance and diagnostics tool for the MySQL database server” (http://www.jetprofiler.com/) that is a bit expensive: $399. If the price isn’t your only criteria for choosing development tools, this is the best choice..and the only one, because the alternatives seem to be simple tools for reading MySQL logs, which is not what you wished for.

If you are using a Version Control System, like Git, you can use an Eclipse add-on, or you might as well use SmartGit for $69, which is an easy-to-use GUI Git client.

Now for the File Manager, here is a delicate subject, because there is no cross-platform complete solution. For example, if you are using Windows, Total Commander is the best tool you can get and it only costs $46. For Mac OS X, Vladimir Stanciu which was a colleague of mine at IBM and an Objective C ninja, is promising a lot with his Xmander app – hope to see lot more from it in the future. For Linux, I don’t have the slightest idea of a good File Manager.

The web browser that fits best for PHP development and for the future in general is Google Chrome. It’s fast, stable, web store enabled and now it has Mozilla’s most wanted development tool: FireBug which at the time of writing is only at it’s “Lite” version.

With all this tools, you can consider yourself platform independent and ready to code (not just ready to edit text a.k.a. Vim). What else can you dream to have, ehh?

Happy coding!

Closure in PHP

Q: Is Closure the same thing as Anonymous Functions, in PHP?
It appears that yes…at least from what is written on http://php.net/manual/en/functions.anonymous.php

A: A definition for Closure, applicable also for PHP, can be found here: http://en.wikipedia.org/wiki/Closure_(computer_science) – no sign of “Anonymous Functions”, just in the context of “closure is often mistakenly used to mean anonymous function” :)

However, on http://php.net/manual/en/functions.anonymous.php, “Example #3 Closures and scoping” section you can find a good code example on Closure. I’ll point out the things that matters:
- the parameter &$total is passed by reference, so that the value of the parent-local variable $total could keep the sum of all the products in the cart;
- the keyword “use” if used together with Anonymous Functions, allows importing variables into the scope of the function;
- $callback = function ($quantity, $product) use ($tax, &$total)… creates the Closure, which means that the local variable $total is “closed” in the same scope as the Anonymous Function and persists even after returning from it.

The conclusion: php.net is not so evil after all ^^
See ya!

Multiple Inheritance suddenly more easy in PHP (than in C#)

Nor C#, neither PHP supports multiple inheritance, but in both programming languages there are workarounds. A nice article for simulated multiple inheritance in C# you can find here http://www.codeproject.com/KB/architecture/smip.aspx, and in PHP here http://www.serversidemagazine.com/php/how-to-inherit-from-multiple-objects-workaround/.
However, today I realised that none of the problems that can be caused by strong-typed programming languages in multiple inheritance can ever be found in PHP (doh!), which is a weak-typed programming language. Let me show you why.
Since declaration type doesn’t exists in PHP, you can’t have a construction like this:

BClass $var_a = new AClass();

instead, the correct syntax in PHP is:

$var_a = new AClass();

So keeping this in mind, in PHP if two classes BClass and CClass redefine a method m1() from AClass, and DClass inherits from BClass and CClass,

then you’ll never have to worry about constructions like:

CClass $var_d = new DClass();
$var_d->m1();

..because in PHP they simply not exists :) Anyway, it would be interesting in the future to see in PHP construction like in JavaScript: BClass.prototype = new AClass();

Custom implementation for Wall, Messages and Like Button

The ubiquitous “Like” button…it’s nice, easy to use and pretty damn useful.
If you ever wondered how can you implement it on your website..well, it’s very easy and it’s called Facebook Like Button: http://developers.facebook.com/docs/reference/plugins/like/, but that’s not what I want to talk about here.
Now, imagine the next situation: you have a simple web application in which each user can have a Wall and can post on each other’s or on its own wall. What? Facebook has this functionality too :) ?
Currently being my first language of choice, I will explain the implementation in PHP / MySQL. First of all, you should have a simple database structure, like the one below:

Just to be sure you understood the structure:
- a user (wall_entries.user_id) can post a message (wall_entries.message) on other’s or on it’s own (wall_entries.to_user_id) wall;
- the message (wall_entries_likes.wall_entry_id) can be liked/unliked by others or by the author (wall_entries_likes.user.id);
- the message can have one or more replies (wall_entries_messages.message) from others or from the author (wall_entries_messages.user_id);
- the replies (wall_entries_messages_likes.wall_entry_id) can be liked/unliked by others or by the author (wall_entries_messages_likes.user_id)
Let’s take a look at the SELECT statement which retrieve all the messages and likes that belongs to the first user Wall:

SELECT
`wall_entries`.`id`,
`wall_entries`.`user_id`,
`users`.`name` AS `user_name`,
`wall_entries`.`to_user_id`,
`u2`.`name` AS `to_user_name`,
`wall_entries`.`message`,
(
SELECT
GROUP_CONCAT(user_id)
FROM
wall_entries_likes
WHERE
wall_entry_id = wall_entries.id
GROUP BY
wall_entry_id
)AS `user_ids_liked`,
(
SELECT
GROUP_CONCAT(users. NAME)
FROM
wall_entries_likes
INNER JOIN users ON users.id = wall_entries_likes.user_id
WHERE
wall_entry_id = wall_entries.id
GROUP BY
wall_entry_id
)AS `user_names_liked`
FROM
`wall_entries`
LEFT JOIN `users` ON users.id = wall_entries.user_id
LEFT JOIN `users` AS `u2` ON u2.id = wall_entries.to_user_id
WHERE
(to_user_id = '1')


Now, let’s take a look at the SELECT statement which retrieve all the replies and likes of all the messages that belongs to the first user Wall:

SELECT
`wall_entries_messages`.`id`,
`wall_entries_messages`.`wall_entry_id`,
`wall_entries_messages`.`user_id`,
`users`.`name` AS `user_name`,
`wall_entries`.`to_user_id`,
`u2`.`name` AS `to_user_name`,
`wall_entries_messages`.`message`,
(
SELECT
GROUP_CONCAT(user_id)
FROM
wall_entries_messages_likes
WHERE
wall_entry_id = wall_entries_messages.id
GROUP BY
wall_entry_id
)AS `user_ids_liked`,
(
SELECT
GROUP_CONCAT(users. NAME)
FROM
wall_entries_messages_likes
INNER JOIN users ON users.id = wall_entries_messages_likes.user_id
WHERE
wall_entry_id = wall_entries_messages.id
GROUP BY
wall_entry_id
)AS `user_names_liked`
FROM
`wall_entries_messages`
INNER JOIN `wall_entries` ON wall_entries.id = wall_entries_messages.wall_entry_id
LEFT JOIN `users` ON users.id = wall_entries_messages.user_id
LEFT JOIN `users` AS `u2` ON u2.id = wall_entries.to_user_id
WHERE
(wall_entries.to_user_id = 1)


So far, you can clearly see that Tom Cat who posted on John Doe’s Wall got 2 replies from Indiana Jones and John Doe and only the reply from Indiana Jones was liked by two people: John Doe and Tom Cat! Whoaa!
Let’s take a look at the View Helper which generates the HTML near the Like button:

/**
* Generate the HTML that will be displayed near the Like button
*
* @return string
*/
public function wallEntryLikes($item, $userId)
{
$isLikedByCurrentUser = false;
$wallEntryLikesHtml = '';
 
//Check if the wall entry is liked by current user
if(XX_Utilities_Like::getInstance()->isLikedByCurrentUser(
$item, $userId)
){
$wallEntryLikesHtml .= 'You';
$isLikedByCurrentUser = true;
}
 
$userIds = '';
 
//Check if other users like the current wall entry
if($item->user_ids_liked){
$userIds = explode(',', $item->user_ids_liked);
$userNames = explode(',', $item->user_names_liked);
 
//The wall entry is liked by the current user and one or multiple users
if($isLikedByCurrentUser){
if(count($userIds) - 1 == 0){
$wallEntryLikesHtml .= ' like this';
}
else if(count($userIds) - 1 == 1){
$wallEntryLikesHtml .= ' and '
. $userNames[array_search($userId, $userIds)]  . ' like this';
}
else {
$wallEntryLikesHtml .= ' and ' . (count($userIds) - 1) . ' others like this';
}
}
//The wall entry is liked by one or multiple users
else{
if(count($userIds) == 1){
$wallEntryLikesHtml .= $userNames[0] . ' like this';
}
else if(count($userIds) == 2){
$wallEntryLikesHtml .= $userNames[0] . ' and '
. $userNames[1] . ' like this';
}
else{
$wallEntryLikesHtml .= $userNames[0] . ' and '
. (count($userIds) - 1) . ' others like this';
}
}
}
 
if ($wallEntryLikesHtml){
return '' . $wallEntryLikesHtml . '';
}
 
return '';
}

Happy coding!

Method Overloading in PHP

While I was testing some features of PHP in general, I stopped and stared at one OOP principle that seems not to be supported by PHP: method overloading. Because PHP is a weak-typed programming language, method overloading will never be supported, but there is always a workaround.
Example for “method overloading” (another monolithic PHP approach of doing things) in PHP:

function someFunction($param1, $param2 = null)
{
   if(is_array($param1) && isset($param2))
   {
       echo ‘bla’;
   }
   else if(is_int($param1) && isset($param2) && is_string($param2))
   {
       echo ‘bla bla’;
   }
   //else if, else if, else...and so on and so forth
}

The more overloads, the more spaghetti code with lots of if-else if-else statements in a single big boned (not fat! a.k.a Cartman form South Park ;) function.

Between Adjacency List Model and Nested Set Model

There is a time in your project when you need to represent some hierarchical data and the database engine happens to be MySQL and not MSSQL which by the way supports recursive CTEs! and recursive functions.
Now, there is a simple and easy way to represent the hierarchical data, which is fast at insert/update operations and slow at select operations (recursion bleah!) :

Category(
id    INT, PRIMARY KEY, AUTO INCREMENT
name    VARCHAR(50), NOT NULL
parent  INT, NULL
)

..and there is the opposite of the first solution (no recursion, fast select):

Category(
id    INT, PRIMARY KEY, AUTO INCREMENT
name    VARCHAR(50), NOT NULL
lft  INT, NOT NULL
rgt  INT, NOT NULL
)

I will concentrate on the second solution, and just to be sure you get the idea, I will attach an image with a full structure of categories:

The blue text represents the depth of the category and the red text represents the lft and rgt database fields. The green text represents the number of products in the category. You’ll see that if “Portable electronics” has 4 products, the sum of it’s child categories must be equal to 4 (“MP3 Players” + “Flash” + “CD Players” + “2-Way Radios”).
Before you choose the second solution to implement, you should be aware of the complexity of the queries and the conditions for the CRUD operations.
A quick look at the:
- update condition, when moving a category in another category (PHP syntax):

- select statement for categories and number of products (MySQL sytanx):

SELECT
`parent`.`id`,
`parent`.`name`,
`parent`.`visible`,
(
SELECT
COUNT(id)
FROM
categories
WHERE
rgt = lft + 1
AND id = parent.id
)AS is_leaf,
(
SELECT
(COUNT(parent_sub. NAME) - 1)
FROM
categories AS node_sub,
categories AS parent_sub
WHERE
node_sub.lft BETWEEN parent_sub.lft
AND parent_sub.rgt
AND node_sub.id = parent.id
GROUP BY
node_sub.id
ORDER BY
node_sub.lft
)AS depth,
COUNT(products.id)AS products_count
FROM
`categories`
CROSS JOIN .`categories` AS `parent`
INNER JOIN .`products`
INNER JOIN `categories_products` ON products.id = categories_products.product_id
WHERE
(
categories.lft BETWEEN parent.lft
AND parent.rgt
)
AND(
categories.id = categories_products.category_id
)
AND(categories.store_id = 1)
GROUP BY
`parent`.`id`,
`parent`.`visible`
ORDER BY
`parent`.`lft` ASC
LIMIT 100

and the result..

…and if you want to wrap it nicely, in a tree control, it should look like this:

So the conclusion (from the perspective of a .NET / C# developer and a PHP / Zend Ninja wannabe :) ? Doesn’t matter if you work with MySQL, SQL Server, PHP, Zend, .NET C#, you can always find a way to get the best of it.