Site Architecture: How far to take Processwire "is the database"?

[bracketfire]

I'm planning on using PW for an upcoming project.  Normally I plan out the key database tables I need, but now that I've been reading about PW and how it works, I had the usual epiphany.  If all of PW is custom fields, and I create a Template to represent a group of fields, then PW "is the database".  Is it true that rather than creating a "location" table, I'd create a location template and set it up with the address fields, for example?  If so...

How far should one take this methodology?  Can you go to deep with this and live to regret it?   How easy/efficient is it to query custom fields from other backend tools that aren't part of the site?  Do you just bootstrap PW and work with the "pages" that represent the "table rows"?  How about querying with straight SQL?

I guess what I'm trying to ask is, should I go "ALL IN", leveraging PW to the max this way, or build on custom tables in the "usual way" you'd do with other CMS's?

Thanks in advance for any advice on this, I'm blown away by how PW works, but a little concerned about going "all in", so to speak.

[diogo]

Hi bracketfire, welcome to PW.
 
You can go "all in" without any concerns, and use the API without even thinking of the database.
 
 

How easy/efficient is it to query custom fields from other backend tools that aren't part of the site?

The API is very efficient when bootstraped, and It's this easy:

include("/path/to/processwire/index.php");
 
$address = wire('pages')->get("/location/")->address;
 

[bracketfire]

Thank you for this quick response.  I'm convinced of it's elegance in the common, simpler cases.  What concerns me is how PW stores these custom fields (I haven't dug into the schema yet), and how challenging it gets when you have to look up, say "select all auctions whose status is closed and last_modified is greater than some time, and the seller_id is foo order by closed_ts asc".

Or doing Joins or using cross-tables for many-to-many relationships.  I'm worried when the going gets complicated, do you write SQL directly and how easy is the schema for custom fields to deal with?

[Martijn Geerts]

I can hear from your post that you're an experienced developer. Give your self some time, play around with the API. You'll see that PW handles one to may, many to many relations very well. After a while you'll see that PW is a framework with a admin build on top.

If you wish to use SQL directly you're free to use it. Best to use $database (PDO) variable instead of $db (mysqli). 

ps, I'm very bad with SQL, so ProcessWire is heaven for me.

[diogo]

The database is structured to be easily queried by the API and not to be queried directly. So, even if you can do it, I'm pretty convinced that you can do 99% (not only simpler cases) of what you will ever need by using the API.

[adrian]

A post from Ryan that I think is relevant here:

http://processwire.com/talk/topic/4442-pw-performance-with-hundreds-of-fields/?p=43643

I have a similar dilemma coming up and I think I will end up going with a custom fieldtype because it will allow the flexibility of a custom database table structure while still being able to use the PW API effectively. 

Keep in mind that I have been developing with PW for 6 months and this is the first time I have wanted to go this route - generally PW's fields and pages do a great job. Every now and then I have had to use an SQL query and there are a few examples of how to do that here in the forum.

http://processwire.com/talk/topic/1684-reading-and-displaying-data-from-a-custom-table/

http://processwire.com/talk/topic/669-using-a-secondary-database/